Multiple CSV to Access

[Rajesh]

Hello to everyone,

I have several csv, around 100, All csv in same format, like Date, time, open, high, low, close, volume,

I need these csv to merge in Access table, with that i need one more column as ticker in each row of access table, ticker means part of csv file name for example : CVX_60 is the filename, i want CVX as value for all rows for that csv similarly for all files, Here filename is also standard, it will be like tickername_somenumber

[June7]

Options:

1. import csv directly to table

2. link to csv then run INSERT SELECT query to append records to permanent Access table

3. open the csv file as an object in VBA and read each line of the text file and parse the string into fields

[Rajesh]

Thanks June7

Problem is i don't have experience in Access and also my manager wants in Macro code only , i have some code, that used in Excel macro, that will import all xls to access table.

Here i need to import csv to access and also one extra column for each row as file name as new column (Only half of the file name)

Please Assist

[June7]

What do you mean by 'macro'? In Access, macro and VBA are very different. A macro in Excel is really just VBA code.

Why would you make a new field for file name? Creating a new field with each import indicates a non-normalized data structure and will give you problems in the future. Similar issue as your other thread.

[Rajesh]

Thanks June7

Regarding new field because everytime we will get one file, filename is like HES_60.csv, here HES is ticker name, we want to save that information with each row, till entire file copied to access, then another file and that ticker name, so on.

[June7]

Save the ticker name - yes, but I hope you don't mean into a separate new field for each. Have a field 'TickerName' and populate the new records with the value. How that is accomplished depends on the import method.

[Rajesh]

Save the ticker name - yes, but I hope you don't mean into a separate new field for each. Have a field 'TickerName' and populate the new records with the value. How that is accomplished depends on the import method.

Hi June7

Actually , i am getting 7 fields value from csv, similarly i have around 1000 rows in csv, i have several files in the folder, I want to export these fields to access table to along with new column as filename for each row. (ticker name)

Date, time, open, high, low, close, volume and tickername

[June7]

So have you attempted one of the 3 options described in post 2 to accomplish import of 1 file?

If you want VBA to cycle through the files in a specified folder and automatically import all csv files and then update the additional tickername field, that can all be accomplished. These techniques have been discussed in numerous forum threads.

How the files are grabbed depends on how the file names are known. You say the file names are a standard structure with a numeric suffix. The suffix can be provided by user entry to a textbox. Then code can reference the textbox. Example for option 1:

DoCmd.TransferText acImportDelim, "YourCustomSpecificationName", "tblImport", "C:\SomeFolder\DataFile" & Me.textbox & ".csv", False

[Rajesh]

So have you attempted one of the 3 options described in post 2 to accomplish import of 1 file?

If you want VBA to cycle through the files in a specified folder and automatically import all csv files and then update the additional tickername field, that can all be accomplished. These techniques have been discussed in numerous forum threads.

How the files are grabbed depends on how the file names are known. You say the file names are a standard structure with a numeric suffix. The suffix can be provided by user entry to a textbox. Then code can reference the textbox. Example for option 1:

DoCmd.TransferText acImportDelim, "YourCustomSpecificationName", "tblImport", "C:\SomeFolder\DataFile" & Me.textbox & ".csv", False

Thanks June7 for reply

I checked some of the forums, i found the code that will import all csv to access, but i don't know how to add my new column . As i am not expert in Code

[June7]

Adding data to the tickername field would be an UPDATE query. Will it always be the first 3 characters of file name?

CurrentDb.Execute "UPDATE tablename SET tickername='" & Left(strFilename, 3)& "' WHERE tickername Is Null;"

Run that code immediately after the import of a file, which I assume you are doing within a VBA looping structure.