Import csv to different tables

[Angus]

I have a csv file that need to import to Access database.

The csv carries two types of data: PO header and PO lines. They are in
different format and length, which should be imported into two different
tables: tbl_header and tbl_line.

The PO header is customer name, address, etc.

The PO lines are what products are ordered.

The length of header is order type (3), order ID (4), customer name (20)…
The length of lines are order type (3), order ID (4), line number (2), item
description (20), qty (2), price (##.##).

In following case John Stewood purchases one wireless mouse worth $15.00 and
one keyboard worth $3.5, order ID are 1000-01 and 1000-02.

Following is the example of csv
HDR1000JOHN STEWOOD

LIN100001WIRELESS MOUSE 1 1500
LIN100002KEYBOARD 1 0350

How to write the codes?

 

[Alex Dybenko]

Hi,
I think you need to read file line by line using LineInput, check what is a
type for current line and update appropriate table

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Angus]

Do you have example code about LineInput?

I have little experience in upload excel file to access by ADO but didn't
know about LineInput.

 

[Joel]

This is an Excel programming webpage. I think you should post this on the
Access Database Group. I don't think you want to use excel to import the
data to access.

 

[Alex Dybenko]

Hi,
here an example from help:

Dim TextLine
Open "TESTFILE" For Input As #1 ' Open file.
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, TextLine ' Read line into variable.
Debug.Print TextLine ' Print to the Immediate window.
Loop
Close #1 ' Close file.

so you need to check what kind of line you are getting in TextLine and then
get required values from it

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Angus]

Thanks, I will have a try and get back to you.

Hi,
here an example from help:

Dim TextLine
Open "TESTFILE" For Input As #1 ' Open file.
Do While Not EOF(1) ' Loop until end of file.
Line Input #1, TextLine ' Read line into variable.
Debug.Print TextLine ' Print to the Immediate window.
Loop
Close #1 ' Close file.

so you need to check what kind of line you are getting in TextLine and then
get required values from it

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Angus]

Hi Alex,

My file looks like following, but I have to copy the lines to different
tables depends on the first 3 characters of each line: HDR --> tbl_header,
LIN --> tbl_line.

HDR1000JOHN STEWOOD
LIN100001WIRELESS MOUSE 1 1500
LIN100002KEYBOARD 1 0350

eg, for the first line, copy the first 3 characters (HDR) to HDR field of
tbl_header, and second 4 characters (1000) to Order_ID of tbl_header...

for the second line, copy the first 3 characters (LIN) to HDR field of
tbl_line, and second 4 characters (1000) to Order_ID of tbl_line, and the
coming 2 characters (01) to Line_No of tbl_line...

How doesthe code look like?

 

[Alex Dybenko]

Hi,
something like this:

set rstH=currentdb.openrecordset("Select * from tbl_header",dbopendynaset)
Select case left(TextLine,3)
case "HDR"
rstH.addnew
rstH!HDR =mid(TextLine,4,3)
rstH!Order_ID =mid(TextLine,8,4)
rstH.update
case "LIN"
...
end select


--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Angus]

Hi Alex,

Thanks for your reply.

Can I open two recordsets for tbl_header and tbl_line, and case "HDR" update
data to tbl_header and case "LIN" update to tbl_line?

 

[Douglas J. Steele]

Yes.

 

[Angus]

Hi Doug,

it works well, thank you. Except some little bugs i want to ask.

1. When the program write "NC27272" to state field and zip field, it only
write "N" to state field instead of "NC"; my code is:

rsHD!State = Mid(TextLine, 150, 2)

however it can write "NC" if I change it to rsHD!State = Mid(TextLine, 150,
3), but the length of state should be 2, why's that happen?

2. The PO number is 071378 but it becomes 71378 in Access even the format is
text, how to make it exactly the same as what it is (start with "0" if there
is a "0")? my code is as followed:

rsHD!CUST_PO = Mid(TextLine, 172, 20)

Thanks for your help.

 

[Douglas J. Steele]

1. When the program write "NC27272" to state field and zip field, it only
write "N" to state field instead of "NC"; my code is:

rsHD!State = Mid(TextLine, 150, 2)

however it can write "NC" if I change it to rsHD!State = Mid(TextLine,
150,
3), but the length of state should be 2, why's that happen?

Are you sure you're not getting " N", rather than "N"? (In other words, are
you sure it starts in position 150, and not 151)?

2. The PO number is 071378 but it becomes 71378 in Access even the format
is
text, how to make it exactly the same as what it is (start with "0" if
there
is a "0")? my code is as followed:

rsHD!CUST_PO = Mid(TextLine, 172, 20)

Is CUST_PO a numeric field, as opposed to a text field? Numeric fields do
not keep leading zeroes: for a numeric field, 071378 and 71378 are
identical. I'm assuming CUST_PO is the customer purchase order number, so
it's doubtul you'll ever be doing arithmetic with the value!

 

[Angus]

Are you sure you're not getting " N", rather than "N"? (In other words, are
you sure it starts in position 150, and not 151)?

You are right, this is my mistake, I changed it to 151 now it's ok.

Is CUST_PO a numeric field, as opposed to a text field? Numeric fields do
not keep leading zeroes: for a numeric field, 071378 and 71378 are
identical. I'm assuming CUST_PO is the customer purchase order number, so
it's doubtul you'll ever be doing arithmetic with the value!

the format is given as text format, and it comes with 071378, I hope to
keep it as text format so in future I have a chance to map it with original
data.

 

[Douglas J. Steele]

the format is given as text format, and it comes with 071378, I hope to
keep it as text format so in future I have a chance to map it with
original
data.

You didn't answer the question. What is the data type of the field CUST_PO
in the table that's part of recordset rsHD? If you're losing leading zeroes,
it must be Number, not Text. Change it to Text, and your problem will go
away.

 

[Angus]

Got it, thanks. It is done now.

You didn't answer the question. What is the data type of the field CUST_PO
in the table that's part of recordset rsHD? If you're losing leading zeroes,
it must be Number, not Text. Change it to Text, and your problem will go
away.