Import Multiple Text Files to Access Table Using VBA Method

[buggsbunny4]

Hi,

I want to import multiple text files to Access 2007 Table called "Data_Import" using VBA code. The text file names are not same so I the are located in a folder. So I should be able to select them through windows. When i import the text file, the first Row of the table should contain the file path along with the file name and the second row should just contain the file name and the data from the text file should be appended after that. The same procedure should be followed for subsequent text files.

Any Help would be appreciated.



Thanks
rk

[ssanfu]

Not a lot of info to go on.

What is the structure of the import table?
What does the text file look like (maybe post 3 - 4 lines of data??)

One thing worries me:

When i import the text file, the first Row of the table should contain the file path along with the file name and the second row should just contain the file name and the data from the text file should be appended after that.

You do realize that a table has no order. It is a "bit bucket". The order you add the records is not necessarily the same order that you will view the data.

If you want "the file path along with the file name" and "the second row should just contain the file name", instead you should have another field in the table to store the file path (the file name can be calculated). Each record added from a text file would have the file path stored in that record.

[June7]

Importing text file is common topic. Search the forum 'import text' with Advanced search. I have responded to quite a few, so if you want to narrow the search, add criteria for June7 poster.

Here is one https://www.accessforums.net/program...ion-23820.html

[buggsbunny4]

Hi Steve,

The data in the text file will be just bunch of strings. The data will be just imported in to one access field which is a memo field. The reason I wanted this way is once the data is imported into the table, I need to extract he data from that table and move to different tables. So the first step is to just import the data. The format to be used to import the text data is fixed.

I have attached the sample text files and a sample database how I would like to see the text file data in the database table.

Thanks
rk

Not a lot of info to go on.

What is the structure of the import table?
What does the text file look like (maybe post 3 - 4 lines of data??)

One thing worries me:

You do realize that a table has no order. It is a "bit bucket". The order you add the records is not necessarily the same order that you will view the data.

If you want "the file path along with the file name" and "the second row should just contain the file name", instead you should have another field in the table to store the file path (the file name can be calculated). Each record added from a text file would have the file path stored in that record.

[June7]

Presume the table Data_Import is the intermediate table and not 'final' table? Apparent text file not structured for import by wizard directly to final table(s).

Personally, I would write custom code to import directly to final table and not bother with intermediate table. The file has consistency in structure so code should not be so difficult.

Regardless of approach, will need code to parse the lines of data. I think it would be easier to parse the text file string than the Access memo field, although it is tab delimited. Never tried code to parse tab delimited string.

How did the lines with path/filename get into the table - manual entry? I agree with ssanfu, path/filename should not be a single record, should be a field and is populated for each row of data imported. Might also need field for Section, unless each section has a different destination table, which makes sense because each section has different number of fields. You did not provide the 'final' table(s) but consider table structure:

tblTest
ID (primary key)
CreatedBy
CreatedDate
TestData
FilePathName

tblTestData
TestID (foreign key)
Section
PrimaryKey1
PrimaryKey2
PrimaryKey3
TestData1
TestData2
TestData3
TestData4
TestData5
TestData6

Multiple similar field names is an indicator data is not fully normalized. And I am confused by Section3 which looks very different from the other 2.

No code included with posted db. Attempt code and if you encounter issue, post question with code for analysis.

[buggsbunny4]

I already have the code in excel to import the files and parse the data. But the problem is I am reaching excel limitation of 1 million rows so I thought I can use Access to remove that limitation. The Path/Filename I have in the table is from my excel macro which adds that to the spreadsheet before importing the text file. I am ok with directly going to the final table without intermediate table but as I am new to Access code I am not sure how to accomplish this. As far as the sections are concerned, yes, each section will have it's own table.

Is there any way we can convert the excel vba to Access vba code?

Thanks
rk

[June7]

The Excel code is writing data to cells of worksheet, right? The Access code would write data to fields of table, either through a recordset or directly to table with sql action (CurrentDb.Execute "INSERT ..."). No, I doubt the code is transferrable. Refer to the link I provided for examples of code for Access import.

[buggsbunny4]

yes, thats correct. The excel code is writing the data to the cells of worksheet.

[June7]

On second thought, using a temp table might be an easier approach for you. The import wizard can handle a tab delimiter so it could import the data into columns. The import should create an autonumber unique ID so you can sort the records in the order they were read into the table. However, think will need to use VBA and recordset to write data to the 'final' table(s). Open recordset of the temp table imported data, cycle through the records and conditional code will determine if the record should be written and to which fields. No idea how long this will take for a million records.

Is the spreadsheet structured exactly like the text file? Perhaps an even easier approach would be to prep the spreadsheet so its column structure is the same as the Access table then just import or link with the wizard. At least for the million records you already have in the spreadsheet. This doesn't address how you would deal with future text file imports. Continue using Excel for intermediate processing or straight to Access?

AFAIK, Access does not have a limit on number of records but it does have a file size limit of 2gb.

[ssanfu]

I won't be able to look at the database until tonight, so I can't look at the code or structure of the tables. If your table structure is like the text file, you might want to normalize your tables.

[buggsbunny4]

Hi, I started parsing the text file but reached a snag. I am not sure how I need to code it. As my text file has different sections, and each section will be it's own database table. So when I am reading the text file when I reach Section1, I need to parse the data in Section1 to the section1 table excluding headers from section1 as I already defined the headers in the table and similarly for Section2 and Section3. I am able to loop it and parse it at the same time.

Any suggestion/help will be greatly appreciated.

Thanks
rk

On second thought, using a temp table might be an easier approach for you. The import wizard can handle a tab delimiter so it could import the data into columns. The import should create an autonumber unique ID so you can sort the records in the order they were read into the table. However, think will need to use VBA and recordset to write data to the 'final' table(s). Open recordset of the temp table imported data, cycle through the records and conditional code will determine if the record should be written and to which fields. No idea how long this will take for a million records.

Is the spreadsheet structured exactly like the text file? Perhaps an even easier approach would be to prep the spreadsheet so its column structure is the same as the Access table then just import or link with the wizard. At least for the million records you already have in the spreadsheet. This doesn't address how you would deal with future text file imports. Continue using Excel for intermediate processing or straight to Access?

AFAIK, Access does not have a limit on number of records but it does have a file size limit of 2gb.

[June7]

Code will involve nested conditional statements and looping structure and use of variables. As example, if the text string is Like "Section*" and is not already equal to the value of variable then set the variable to the SectionX string and write the following lines to appropriate table until the next "SectionX" is encountered. Each line will have to be tested for the strings "Section" and "Primary" and "Column" and then deal with the line and those that follow as appropriate, which could be to skip the header lines or write the data lines. The code is not really difficult but is an exercise in logic programming (If something is this then do this else if this do that else if etc... repeat for next line).

[ssanfu]

I cut and pasted code that I use to import text files as an example of how to import text files.

I created a test table and renamed the field "Data" to "ImportData" since "DATA" is a reserved word in Access.
I created a from and added two buttons - one to import the text file and one to clear the test table.
If you have questions about the code, post back.

Also, I MUST stress that a table has no order. It is a bit bucket. There is no guarantee that the order that you input the data will be the order that you read the data. You need another field that allows you to ensure the data can be read in the correct order.

[buggsbunny4]

Hi Steve,

Thanks for the code. The order of the data is just fine as I need to move the data into different tables for each individual section. That's where I hit a snag. I am not sure how to write a code by looping to write all the section1 data into section1 table and section2 data to section2 table.

Thanks
rk

I cut and pasted code that I use to import text files as an example of how to import text files.

I created a test table and renamed the field "Data" to "ImportData" since "DATA" is a reserved word in Access.
I created a from and added two buttons - one to import the text file and one to clear the test table.
If you have questions about the code, post back.

Also, I MUST stress that a table has no order. It is a bit bucket. There is no guarantee that the order that you input the data will be the order that you read the data. You need another field that allows you to ensure the data can be read in the correct order.

[ssanfu]

OK.......

How about a dB with the different tables for each individual section and some sample data in them?

Do you have a text file with actual data? I don't think you really have a field named "PrimaryKey" with data "Key1", "Key2",...