Importing multiple csv files.

[zealot]

Hello friends!
I have a list of 250 CSV files. I need to import each of these into their own worksheet (e.g. create worksheet ‘Apple” for file Apple.csv. The data should be placed at cell A1, separated by comma and import parameters saved – do you think VBA can automate this? I have attached a few sample CSV files…Any help hugely appreciated!

list of files uploaded:

EURAUD.csv
DAX.csv
3M.csv
SOYUSD.csv
Alcoa.csv


Dave

[jindon]

Try

Please Login or Register  to view this content.

[zealot]

thank you for this beautiful code....

I have noticed two issues - can you please help:

1) The import parameters are not saved with this code - I will need to refresh this data later on so I need to save the query
2) I am getting this error for the files with the periods:

Microsoft Visual Basic —
Run-time error '-2147217865 (80040e37)':
The Microsoft Access database engine could not find the object
‘.E.bay_1440.csv’. Make sure the object exists and that you spell its
name and the path name correctly. If '.E.bay_1440.csv’ is not a local
object, check your network connection or contact the server h
administrator.

the actual name of the file is <E.bay_1440.csv> - not sure why it is adding the period to the start of the file...

thanks a lot for your wonderful help Jindon!

[jindon]

Then try this.

Please Login or Register  to view this content.

[zealot]

Thank you Jindon - you must be in Tokyo - so some lag between responses...

thank you for this beautiful macro - always amazed at how such a short macro can do so much!!

The macro import all fine - it just does not keep the import parameters - how can I keep connections to data so that I can refresh the data afterwards by just pressing REFRESH ALL CONNECTIONS?

[jindon]

It should update all the data by REFRESHALL.

[hrlngrv]

Another alternative,

Please Login or Register  to view this content.

All this does is open each .CSV file in the indicated directory, then move it to the end of the workbook containing the macro. If a worksheet with a given name already exists, a new one would be added with (2), (3), etc appended to the worksheet name, so none would be skipped.

[zealot]

It should update all the data by REFRESHALL.

Hello Jindon - I tried to run it again and it is not saving import parameters (*which normally show when you right click on imported columns) - there are no connections in data view and refresh all does not refresh the imported data - can you please help?

[zealot]

Another alternative,

Please Login or Register  to view this content.

All this does is open each .CSV file in the indicated directory, then move it to the end of the workbook containing the macro. If a worksheet with a given name already exists, a new one would be added with (2), (3), etc appended to the worksheet name, so none would be skipped.

thanks - does it save import parameters which can then be used to update the files?

[jindon]

Once you run the code, select any sheet that shows the imported data and [Data] - [Query & Connection] - [Property].
You will see the connection property.

It should update anytime you click Refresh all.

[zealot]

It should update all the data by REFRESHALL.

Jindon, I just recorded the macro which saves the import parameters:

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\Selected\PycharmProjects\pythonProject\data\#3I_1440.csv", _
Destination:=Range("$A$1"))
.Name = "#3I_1440"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub


do you think you macro can be updated slightly to save the query parameters?