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

 

[CajunCenturion]

The values of a, b, c, and d do not have to be unique. There is nothing to prevent you from placing the same array element into multiple fields.

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

 

[PHV]

CajunCenturion, field2 and field3 must have the same itemID as field1 in their respective tables, as far as I have correctly understood the goal.

 

[CajunCenturion]

Sorry I didn't get back sooner, but work got in the way. If the ItemID is to be an autonumber field, that it should only be so in the Master table, which in this case, I'm guessing to be the ItemsTable. To carry the same ItemID across all the tables, then you would do the following:

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("ItemID") = lRst_ItemsTable.Fields("ItemID")
      .Fields("FldName1") = lStr_FldVals(c)
      .Fields("FldName2") = lStr_FldVals(d)
      .Update
   End With
Loop

Once you execute the .Update command on the Recordset attached to the ItemsTable, the autonumber value assigned to the ItemID will be available in its entry in the .Fields collection until you move to another record, or another .AddNew.

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

 

[infinitx]

Thank you guys,

Sorry I didn't respond sooner, I didn't have a chance to check back.

I have one question: What would I replace "a,b,c,d" with?
I'm not understanding what the variables represent.

Thanks,

Alex

 

[CajunCenturion]

Since you have a delimited file, you won't be using the Mid function based appropach, as that approach is only for a fixed length file. You'll be using the Split function based approach.

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

 

[infinitx]

So I would leave "a,b,c,d" as they are?

Thanks,

Alex

 

[CajunCenturion]

I'm sorry, I used a, b, c, and d in the Split function as well. a, b, c, and d represent the subscripts into the lStr_FldVals array created by the Split function. I can't tell you the correct values of a, b, c, and d because you have provided and definition of the ASCII file. What is the first field in the ASCII file? What is the exact TableName and Column Name that the first field is to be mapped to? What is the second field in the ASCII file? What is the exact TableName and Column Name that the second field is to be mapped to?

a, b, c, and d represent the nth field in the ASCII file. If a represents the first field in the ASCII file, then it will have a value of 0. If it's the second field, then it will have a value of 1. Remember, the array is zero-based.

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

 

[infinitx]

Could you give me an example of TableName and Column Name?

I'm not sure what you mean.

Thanks,

Alex

 

[CajunCenturion]

Your very first post is a good example of table name and column names. You identify four tables and a couple of columns within each table.

No-one is going to be able to help you further until you provide specific information, specific information about the database tables that need to be updated, and that means providing the actual table name and field names, and details about the layout of the ASCII file, specifically - which field in the ASCII file goes into which field(s) in which table(s).

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