If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
TransferText command - any help appreciated
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 |
#2
|
|||
|
|||
TransferText command - any help appreciated
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 "ac512" wrote in message ... 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 |
#3
|
|||
|
|||
TransferText command - any help appreciated
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" wrote: 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 "ac512" wrote in message ... 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 |
#4
|
|||
|
|||
TransferText command - any help appreciated
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 "ac512" wrote in message ... 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 |
#5
|
|||
|
|||
TransferText command - any help appreciated
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" wrote: 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 "ac512" wrote in message ... 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 |
#6
|
|||
|
|||
TransferText command - any help appreciated
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 "ac512" wrote in message ... 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 |
#7
|
|||
|
|||
TransferText command - any help appreciated
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" wrote in message ... 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 |
#8
|
|||
|
|||
TransferText command - any help appreciated
Thank you so very much - you are indeed a lifesaver & have saved considerable
work. Much appreciated Kind regards AC "kc-mass" wrote: 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" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|