TransferText command - any help appreciated

[ac512]

Hello

I am using access 2007, and every month I have around 200 csv files that I
have to load into a table in my database.
In the past, I have used the TransferText command to automatically upload a
single file into my database, but I have not been able to do so with multiple
files.

The file names have a consistent beginning & end, but the middle is
different, for example 1 file may be called "quotes_nswapp_pif_200905.csv"
and another file may be called "quotes_vic_pif_200905.csv". I have tried
using wildcards and a loop statement so that I reference all of the separate
files so as to load them, but I have not had any success.

Any suggestions/guidance would be greatly appreciated

Thanks in advance
Kind regards
AC

 

[Steve Schapel]

AC,

Will all the csv files be in the same folder? And will there be any other
csv files in that folder, apart form the ones you need for your monthly
import?

If all, and only, the files for import will be in the same folder, then you
can simply loop through them all using the Dir() function, and import them
one by one.

--
Steve Schapel, Microsoft Access MVP

Hello

I am using access 2007, and every month I have around 200 csv files that I
have to load into a table in my database.
In the past, I have used the TransferText command to automatically upload
a
single file into my database, but I have not been able to do so with
multiple
files.

The file names have a consistent beginning & end, but the middle is
different, for example 1 file may be called "quotes_nswapp_pif_200905.csv"
and another file may be called "quotes_vic_pif_200905.csv". I have tried
using wildcards and a loop statement so that I reference all of the
separate
files so as to load them, but I have not had any success.

__________ Information from ESET Smart Security, version of virus signature database 4142 (20090609) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[ac512]

Hi Steve

All the files are in the same folder & there will not be any other files in
the folder.
I will have a go at using the Dir() function - I have not used it before but
I will investigate
I very much appreciate your response

If there is any information you could share re. the Dir() function, I would
be most appreciative.

Thanks again
AC

 

[Steve Schapel]

AC,

Here is a skeleton outline of the code you can use...

' ***
Dim CSVFile As String
CSVFile = Dir("C:\NameOfYourFolder\*.csv")
If Len(CSVFile) Then ' there are files
Do Until Len(CSVFile) = 0
'<your TransferText etc code here>
CSVFile = Dir
Loop
End If
' ***

--
Steve Schapel, Microsoft Access MVP

Hi Steve

All the files are in the same folder & there will not be any other files
in
the folder.
I will have a go at using the Dir() function - I have not used it before
but
I will investigate
I very much appreciate your response

If there is any information you could share re. the Dir() function, I
would
be most appreciative.

__________ Information from ESET Smart Security, version of virus signature database 4142 (20090609) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[ac512]

Hi again Steve

Many thanks for your assistance with this. I tried to run the code you
provided me, obviously adapting it to my database, but I get an error message:
Run-time error '52': Bad file name or number

Any suggestions?
Sorry to bother you again

Kind regards
AC

 

[Steve Schapel]

AC,

No bother. But I think if you could post back with copy/paste of the entire
code you are now using, will help me or someone to spot the problem.

--
Steve Schapel, Microsoft Access MVP

Hi again Steve

Many thanks for your assistance with this. I tried to run the code you
provided me, obviously adapting it to my database, but I get an error
message:
Run-time error '52': Bad file name or number

Any suggestions?
Sorry to bother you again

__________ Information from ESET Smart Security, version of virus signature database 4142 (20090609) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[kc-mass]

If the files are all of uniform structure and all are loaded to the same
table then you can solve the problem another way. From the "Start" menu
select "Run", type in "CMD"
and you will get a DOS like interface.
Change to the directory where your files are
("CD C:\MyDirectory"). Copy all the CSV files into a single file "Copy *.csv
MyBigFile.csv".
All the data from the 200 files will now be in a single file
which you can import as you did before.

Regards

Kevin

 

[ac512]

Thank you so very much - you are indeed a lifesaver & have saved considerable
work.
Much appreciated

Kind regards
AC