Import Data from an Ascii File

[infinitx]

Hi,

I have the following tables in my database:

ItemsTable

Item ID
Item Text
....
....

GeneralInfoTable

Item ID
Item Key
Item Author
....
....

CaseTable

Item ID
Case Text
....

GraphicTable

Item ID
Graphic
....

If the user has all of the data stored in an Ascii file, how could I create an import function that would import the Ascii file data into the appropriate data tables?

Thanks,

Alex

 

[PHV]

Take a look at the DoCmd.TransferText method

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[infinitx]

Hi PHV,

Could you elaborate on that?

Would I create a macro to do this?

For example, my fixed Ascii file is called C:\import.dat. How would I import this file and copy its fields to the appropriate table fields?

Import.dat fields

Field 1 Field 2 Field 3
1 3 4
5 6 7

Table 1

Item Text
1
5

Table 2

Case Text
3
6

Table 3

Graphic
4
7

Thanks,

Alex

 

[PHV]

I didn't realize all the data were on a single file
Anyway I don't understand which data you want to insert in which table fields.

 

[infinitx]

From the import.dat file, I want to to insert the data that is located in Field 1 into Item Text field in Table 1. Also, I want the data that is located in Field 2 to be copied into Case Text field in Table 2. Finally, I want the data in Field 3 to be copied into Graphic field in Table 3.

Is this possible?

Would it be better to split import.dat into 3 separate files? If so, is this easy to do for somebody who is computer illiterate (which is for whom this program is being made for).

Thanks,

Alex

 

[PHV]

You may consider creating a temporary import table receiving the TransferText results and then 3 append queries to populate the respective tables.
BTW you have to figure out the ItemID value

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CajunCenturion]

I would write a function that would create a Recordset for each affected table. Then I would scan the ASCII file, parsing the individual lines into the appropropriate field of the corresponding recordset, and write to the database.

Sub ImportTextfile()

   Dim lRst_ItemsTable  As ADODB.Recordset
   Dim lRst_GenItems    As ADODB.Recordset
   Dim lInt_FileHandle  As Integer
   Dim lStr_SourceLine  As String
   
   Set lRst_ItemsTable = New ADODB.Recordset
   lRst_ItemsTable.ActiveConnection = CurrentProject.Connection
   lRst_ItemsTable.Open "tblItemTable", , adOpenDynamic, adLockPessimistic, adCmdTable
   
   Set lRst_GenItems = New ADODB.Recordset
   lRst_GenItems.ActiveConnection = CurrentProject.Connection
   lRst_GenItems.Open "tblGenItems", , adOpenDynamic, adLockPessimistic, adCmdTable
   
   lInt_FileHand = FreeFile
   Open "c:\import.dat" For Input As #lInt_FileHand
   Do While Not EOF(lInt_FileHand)
      Line Input #lInt_FileHand, lStr_SourceLine
      With lRst_ItemsTable
         .AddNew
         .Fields("FieldName1") = Mid(lStr_SourceLine, a, b)
         .Fields("FieldName2") = Mid(lStr_SourceLine, C, d)
         .Update
      End With
      With lRst_GenItems
         .AddNew
         .Fields("FieldName1") = Mid(lStr_SourceLine, e, f)
         .Fields("FieldName2") = Mid(lStr_SourceLine, g, h)
         .Update
      End With
   Loop
   Close #lInt_FileHand
   
   lRst_ItemsTable.Close
   Set lRst_ItemsTable = Nothing
   
   lRst_GenItems.Close
   Set lRst_GenItems = Nothing
   
End Sub

The above example is merely a shell of the process, using only two recordset, but should provide enough to build the function. How you get from the Source line into the appropriate files is quite dependant on the specific layout of the ASCII file (fixed, deliminted, special formatting, etc). The about shell uses the Mid function assuming a fixed length file, but that would not my choice if this is a delimited file. Without knowing the details, cannot provide a recommendation.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[infinitx]

PHV,

Could you give me an example of this? Thanks. And the Item ID value is an Autonumber so when the data is added, the Item ID will be added accordingly.


CajunCenturion,

Thanks, I'll give that a try. I have one question though, how does the code know that Field 1 data is copied into Item Text, Field 2 data into Case Text, etc.?

Thanks again,

Alex

 

[mph1]

Hi Alex,

Out of curiousity, is this a one time deal or will it be a recurring one????

If it will be recurring, then you might want to think of another way to have this data created which would simplify the situation. Unfortunately, I don't know what is creating the ASCII file.

CajunCenturion is quite right where he states:

"dependant on the specific layout of the ASCII file (fixed, deliminted, special formatting, etc)"

Keep in mind that although, for the short term, you may be able to solve your dilemma, in the long run, you may encounter other problems not anticipated.

Good Luck!

An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Autonumber Description - FAQ702-5106

 

[PHV]

the Item ID will be added accordingly
I agree, in the ItemsTable.
But how about CaseTable and GraphicTable ?
Can we assume that ItemText is unique ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[infinitx]

Thank you for responding!

mph1,

I was hoping that this would be a recurring one! And (usually, and for this purpose) it will be a fixed Ascii file.

PHV,

The ItemsTable, CaseTable, and GraphicTable are all linked by the Item ID so the Item ID will be added automatically for all three tables. Yes, you could assume that the Item Text is a unique field simply because I don't know why the user would enter the same Item Text twice. The Graphic and the Case Text, however, are not unique.

 

[PHV]

So, I assume you know the basics about the TransferText method and the Schema.ini stuff (if not, simply take a look in help and if it not suffice, do a keyword search in the ms-access fora).
You will have a import table with 3 colummns, say (accordly with your Import.dat example):
ImportTable
Field1
Field2
Field3
When the loading of ImportTable is done, you may try something like the following 3 append queries:
INSERT INTO ItemsTable(ItemText) SELECT Field1 FROM ImportTable;
INSERT INTO CaseTable(ItemID,CaseText) SELECT ItemID,Field2 FROM ImportTable INNER JOIN ItemsTable ON Field1=ItemText;
INSERT INTO GraphicTable(ItemID,Graphic) SELECT ItemID,Field3 FROM ImportTable INNER JOIN ItemsTable ON Field1=ItemText;

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[infinitx]

PHV,

How do you create an ImportTable (is that just a regular table or is it an import query?) If it is an import query, how do you create it?

Thanks,

Alex

 

[CajunCenturion]

Thanks, I'll give that a try. I have one question though, how does the code know that Field 1 data is copied into Item Text, Field 2 data into Case Text, etc.?

The program knows which part of the ASCII file goes into which Table.Field because you place the mapping directly into the program.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[infinitx]

CajunCenturion,

Thanks for your help. I have one other question. Is the code that you have posted supposed to be a module? If so, would I code a command button to run that module? In other words, how is the user going to execute the code?

Thanks,

Alex

 

[CajunCenturion]

Although it doesn't have to be in a module, I would put it in a module, and yes, you can trigger the function from the Click event of a command button.

Keep in mind that in the example I posted which is based on a fixed length file, I used a, b, c, and d as the arguements to the Mid function. The actual values of a, b, c, and d will depend on where those fields lie in the text file. If it is a delimited file, then you wouldn't use the Mid function, but rather a different method to extract the individual fields, most probably using the Split function to place the individual items in an array. Until we know the actual specification of the ASCII file, we can't provide specific answers.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[infinitx]

The file is going to be a Delimited, Comma Delimited file.

 

[PHV]

How do you create an ImportTable
I guess it'll be created by the TransferText method.
You may consider to drop them after the 3 append queries, like this:
DoCmd.RunSQL "DROP TABLE ImportTable;"
Or if you want to keep it:
DoCmd.RunSQL "DELETE * FROM ImportTable;"

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CajunCenturion]

The file is going to be a Delimited, Comma Delimited file.

Since we're dealing with a comma delimited file, I would probably adjust the code as follows:

Dim lStr_FldVals() as String

Do While Not EOF(lInt_FileHand)
   Line Input #lInt_FileHand, lStr_SourceLine
   lStr_FldVals = Split(lStr_SourceLine, ",")
   With lRst_ItemsTable
      .AddNew
      .Fields("FldName1") = lStr_FldVals(a)
      .Fields("FldName2") = lStr_FldVals(b)
      .Update
   End With
   With lRst_GenItems
      .AddNew
      .Fields("FldName1") = lStr_FldVals(c)
      .Fields("FldName2") = lStr_FldVals(d)
      .Update
   End With
Loop
Close #lInt_FileHand

The only thing left for you to do is to replace the subscripts a, b, c, and d with the appropriate value to match up the correct TableName.FieldName with the Value from the ASCII file.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[PHV]

CajunCenturion, how you manage the common itemID ?