Automating Importing of CSV Files with dynamic feilds

  • Thread starter Thread starter Lucas_london
  • Start date Start date
L

Lucas_london

Hi,

I need to automate the importing of three CSV files (along with feild names
located in first row) in a folder into serperate tables in Access. Hoevever
the number of feilds in each file will vary/change with each import so I need
access to be able to determine the number of columns to import. I know this
is easy to do manually but I am now looking for a automated VBA solution as I
need to do this several times a week.

The names of the three files are:

1) BOE.csv (23 Cols)
2) Indices.csv (29 Cols)
3)Technicals.csv (21 cols )

The first row in each file contains the fields/columns headings. I do not
want to assign any primary keys. I have found the code below to import files
from a given folder. Could this be adopted in anyway to do what I need?

Thanks

Lucas

Dim strFile As String
Dim strFolder As String

strFolder = "C:\MyFolder\MySubfolder\" ' the final slash is important!
strFile = Dir$(strFolder & "*.txt")
Do While Len(strFile) > 0

strFile = Dir$()
Loop
 
Yes, the code will control the loop to import your csv files. You just need
to change the ".txt" designation to ".csv"
Be aware that for this to get only your 3 files, you can't have any other
files with the .csv file type in the folder, or it will attempt to import
them as well.

In addition, you will have to make allowances for which file is being
imported so you cah import it to the correct table name.
I can't tell from your post whether you mean by "the number of feilds in
each file will vary/change with each import", that one file may have a
different number of columns each time or that because there are 3 files,
there will be 3 different numbers of columns specified - one for each file.

If the number of columns for a specific file will vary, then importing to a
new table will allow for this. You can then create a query to move the data
to the production table.
 
Well, in all fairness that code won't import the files: it's missing the
TransferText statement:

Dim strFile As String
Dim strFolder As String
Dim strTable As String

strFolder = "C:\MyFolder\MySubfolder\" ' the final slash is important!
strFile = Dir$(strFolder & "*.csv")
Do While Len(strFile) > 0
strTable = Left(strFile, InStr(strFile, ".csv") - 1)
DoCmd.TransferText acImportDelim, , strTable, strFolder & strFile, True
strFile = Dir$()
Loop

This assumes that BOE.csv is supposed to be stored in table BOE, Indices.csv
in table Indices and so on.
 
I did not say that, Doug. What I said was "Yes, the code will control the
loop to import your csv files." If I was misleading, I apologize.
 
Hi,

I need to automate the importing of three CSV files (along with feild names
located in first row) in a folder into serperate tables in Access. Hoevever
the number of feilds in each file will vary/change with each import so I need
access to be able to determine the number of columns to import. I know this
is easy to do manually but I am now looking for a automated VBA solution as I
need to do this several times a week.

The names of the three files are:

1) BOE.csv (23 Cols)
2) Indices.csv (29 Cols)
3)Technicals.csv (21 cols )

The first row in each file contains the fields/columns headings. I do not
want to assign any primary keys. I have found the code below to import files
from a given folder. Could this be adopted in anyway to do what I need?

Thanks

Lucas

Dim strFile As String
Dim strFolder As String

strFolder = "C:\MyFolder\MySubfolder\" ' the final slash is important!
strFile = Dir$(strFolder & "*.txt")
Do While Len(strFile) > 0

strFile = Dir$()
Loop
Click to expand...

Hi Lucas,

Something I've found to be of great help in the automation of text files is the
use of a schema.ini file. When using the TransferText method Access will use an
ini file if it exists in the same folder as the text files. This file must
always be named schema.ini and must always be in the same folder as the text
files themselves. In your case the schema file might look something like this:

[BOE.csv]
ColNameHeader=True
Format = CSVDelimited
MaxScanRows=0
[Indices.csv]
ColNameHeader=True
Format = CSVDelimited
MaxScanRows=0
[Technicals.csv]
etc.

Note that MaxScanRows will cause Access to scan the entire file to determine
data types. If you don't want that, omit the MaxScanRows and declare the types
yourself:
[BOE.csv]
ColNameHeader=True
Format = CSVDelimited
Col1=BOEid Text Width 7
.... etc.
Col23=LastColName Long Width 3

Do the number of columns in *each* file remain constant only differing from each
other or will the number of columns change in each of the files? If you don't
know ahead of time how many columns you'll have, you'll probably need to keep
MaxScanRows and do something different with the import.

Perhaps you could import the files twice? Do an initial import just to get the
column names; compare those column names to a local table of all possible column
names (with their associated data types); programmatically rewrite the ini file;
kill the first import and finally perform a new, clean import of the text file.

That kinda sounds like fun.

Check this link for more info on using ini files:
http://tinyurl.com/37cnqj


HTH,
RD
 
Back
Top