Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JPYE
New Member

Bring in Multiple CSVs with different information Build up

I have about 60 CSVs in a folder, they are all different so I do not want to Combine them when I try to load them it creates one query but I want to load them all in and then start creating relationships between the different CSVs. How do I do this? I can see a list of the CSVs but I want to load them all in. 

JPYE_0-1671397475408.png

 

1 ACCEPTED SOLUTION
v-henryk-mstf
Community Support
Community Support

Hi @JPYE ,

 

To load multiple CSVs into a database or data analysis tool, you can use a loop to iterate through the files and load them one by one. Here's a general outline of the steps you can follow:

  • Identify the path of the folder that contains the CSVs. You can use the os module in Python to list the files in the directory and get their paths.
  • Create a loop that iterates through the list of CSV files. For each file, you can use a module like pandas to read the CSV into a dataframe, and then use the appropriate function or method to load the data from the dataframe into the database or analysis tool.
  • After all the CSVs have been loaded, you can then start creating relationships between the different tables or datasets. Depending on the database or tool you are using, you may need to specify the relationships using SQL commands or other functions.

Here's an example of how you could load multiple CSVs into a SQLite database using Python:

import os
import sqlite3
import pandas as pd

# Connect to the database
conn = sqlite3.connect("mydatabase.db")

# Identify the path of the folder that contains the CSVs
folder_path = '/path/to/folder/with/csvs'

# List all the CSVs in the folder
csv_files = [f for f in os.listdir(folder_path) if f.endswith('.csv')]

# Iterate through the CSVs
for file in csv_files:
file_path = os.path.join(folder_path, file)
df = pd.read_csv(file_path)
df.to_sql(file, conn, if_exists='replace')

# Close the connection to the database
conn.close()


This code will read each CSV file into a dataframe using pandas, and then use the to_sql function to load the data from the dataframe into a table in the SQLite database. The table will have the same name as the CSV file. The if_exists='replace' argument specifies that if a table with the same name already exists in the database, it should be replaced with the new data.

You can then use SQL commands to create relationships between the tables as needed.


If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


Best Regards,
Henry


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-henryk-mstf
Community Support
Community Support

Hi @JPYE ,

 

To load multiple CSVs into a database or data analysis tool, you can use a loop to iterate through the files and load them one by one. Here's a general outline of the steps you can follow:

  • Identify the path of the folder that contains the CSVs. You can use the os module in Python to list the files in the directory and get their paths.
  • Create a loop that iterates through the list of CSV files. For each file, you can use a module like pandas to read the CSV into a dataframe, and then use the appropriate function or method to load the data from the dataframe into the database or analysis tool.
  • After all the CSVs have been loaded, you can then start creating relationships between the different tables or datasets. Depending on the database or tool you are using, you may need to specify the relationships using SQL commands or other functions.

Here's an example of how you could load multiple CSVs into a SQLite database using Python:

import os
import sqlite3
import pandas as pd

# Connect to the database
conn = sqlite3.connect("mydatabase.db")

# Identify the path of the folder that contains the CSVs
folder_path = '/path/to/folder/with/csvs'

# List all the CSVs in the folder
csv_files = [f for f in os.listdir(folder_path) if f.endswith('.csv')]

# Iterate through the CSVs
for file in csv_files:
file_path = os.path.join(folder_path, file)
df = pd.read_csv(file_path)
df.to_sql(file, conn, if_exists='replace')

# Close the connection to the database
conn.close()


This code will read each CSV file into a dataframe using pandas, and then use the to_sql function to load the data from the dataframe into a table in the SQLite database. The table will have the same name as the CSV file. The if_exists='replace' argument specifies that if a table with the same name already exists in the database, it should be replaced with the new data.

You can then use SQL commands to create relationships between the tables as needed.


If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


Best Regards,
Henry


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Ashish_Mathur
Super User
Super User

Hi,

You will have to load the CSV files individually and then create a relationship between them.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.