0

Hey I'm trying to import multiple csv files from a folder that i have ( like 1200 csv files)

I was able to create a code that will insert one csv file, but im not quite sure how can I change it to take multiple files by code and not to change the name of the file each time manually.

here is the code:

import pandas as pd

# Import CSV
data = pd.read_csv (r'D:\USA-Data\USA\2007\ORATS_SMV_Strikes_20070131.csv')   
df = pd.DataFrame(data, columns= ['ticker','stkPx','expirDate','yte','strike','cVolu','cOi','pVolu','pOi','cBidPx','cValue','cAskPx','pBidPx','pValue','pAskPx','cBidIv','cMidIv','cAskIv','smoothSmvVol','pBidIv','pMidIv','pAskIv','iRate','divRate','residualRateData','delta','gamma','theta','vega','rho','phi','driftlessTheta','extVol','extCTheo','extPTheo','spot_px','trade_date'])
df = df.fillna(0)
print(df)
# Connect to SQL Server
import pyodbc

conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=WIN7-PC;'
                      'Database=USA;'
                      'Trusted_Connection=yes;')
cursor = conn.cursor()


# Insert DataFrame to Table
for row in df.itertuples():
    cursor.execute('''INSERT INTO USA.dbo.USAData(ticker,stkPx,expirDate,yte,strike,cVolu,cOi,pVolu,pOi,cBidPx,cValue,cAskPx,pBidPx,pValue,pAskPx,cBidIv,cMidIv,cAskIv,smoothSmvVol,pBidIv,pMidIv,pAskIv,iRate,divRate,residualRateData,delta,gamma,theta,vega,rho,phi,driftlessTheta,extVol,extCTheo,extPTheo,spot_px,trade_date)VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)''',
                row.ticker, 
                row.stkPx,
                row.expirDate,
                row.yte, 
                row.strike,
                row.cVolu, 
                row.cOi,
                row.pVolu,
                row.pOi, 
                row.cBidPx,
                row.cValue, 
                row.cAskPx,
                row.pBidPx,
                row.pValue, 
                row.pAskPx,
                row.cBidIv, 
                row.cMidIv,
                row.cAskIv,
                row.smoothSmvVol, 
                row.pBidIv,
                row.pMidIv, 
                row.pAskIv,
                row.iRate,
                row.divRate, 
                row.residualRateData,
                row.delta, 
                row.gamma,
                row.theta,
                row.vega, 
                row.rho,
                row.phi,
                row.driftlessTheta, 
                row.extVol,
                row.extCTheo,
                row.extPTheo, 
                row.spot_px,
                row.trade_date
                )
conn.commit()

the name of the docs goes like this :

ORATS_SMV_Strikes_20070201.csv ORATS_SMV_Strikes_20070202.csv ORATS_SMV_Strikes_20070205.csv

and so on...

would really appriciate all the help i can get!

Gaming World
  • 23
  • 4
  • Ok, let's think a bit. You already have a section that does `for in : `. Does this look like a pattern that could be useful? – Ture Pålsson Jul 11 '21 at 09:48
  • There is e. g. the "glob" module with a function of same name to create a list of files in a given directory. You can then process the files in a loop. – Michael Butscher Jul 11 '21 at 09:49
  • @MichaelButscher i have no idea on how to create that list of files in the given directory... it has been a long time since i coded... – Gaming World Jul 11 '21 at 09:54
  • You can read the Python docs about "glob" and play around with it in an interactive Python shell like the one provided by "Idle" (included in Python installation). – Michael Butscher Jul 11 '21 at 10:00
  • If all the files are present in one folder, then use the python os module and loop over all the csv files present in that folder. But the tricky part here will be building the table DDL's and insert statements dynamically while looping through each file. – Teja Goud Kandula Jul 11 '21 at 10:09
  • https://stackoverflow.com/q/3207219/2144390 – Gord Thompson Jul 11 '21 at 10:23

0 Answers0