| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.access.modulesdaovba.ado,microsoft.public.excel.programming
|
|||
|
|||
Import csv to different tables
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? |
|
#2
Posted to microsoft.public.access.modulesdaovba.ado,microsoft.public.excel.programming
|
|||
|
|||
|
Import csv to different tables
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" wrote in message ... 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? |
|
#3
Posted to microsoft.public.access.modulesdaovba.ado,microsoft.public.excel.programming
|
|||
|
|||
|
Import csv to different tables
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. "Alex Dybenko" wrote: 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" wrote in message ... 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? |
|
#4
Posted to microsoft.public.access.modulesdaovba.ado,microsoft.public.excel.programming
|
|||
|
|||
|
Import csv to different tables
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. "Angus" wrote: 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. "Alex Dybenko" wrote: 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" wrote in message ... 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? |
|
#5
Posted to microsoft.public.access.modulesdaovba.ado,microsoft.public.excel.programming
|
|||
|
|||
|
Import csv to different tables
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" wrote in message ... 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. "Alex Dybenko" wrote: 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" wrote in message ... 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? |
|
#6
Posted to microsoft.public.access.modulesdaovba.ado,microsoft.public.excel.programming
|
|||
|
|||
|
Import csv to different tables
Thanks, I will have a try and get back to you.
"Alex Dybenko" wrote: 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" wrote in message ... 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. "Alex Dybenko" wrote: 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" wrote in message ... 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? |
|
#7
Posted to microsoft.public.access.modulesdaovba.ado,microsoft.public.excel.programming
|
|||
|
|||
|
Import csv to different tables
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" wrote: 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" wrote in message ... 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. "Alex Dybenko" wrote: 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" wrote in message ... 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? |
|
#8
Posted to microsoft.public.access.modulesdaovba.ado,microsoft.public.excel.programming
|
|||
|
|||
|
Import csv to different tables
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" wrote in message ... 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" wrote: 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" wrote in message ... 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. "Alex Dybenko" wrote: 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" wrote in message ... 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How import multiple tables from same web page (Web Query)? | Excel Discussion (Misc queries) | |||
| Import data from multiple tables | Excel Programming | |||
| I can import Access Tables. But, I can't import Access queries | Excel Programming | |||
| Import Queries instead of Tables (ADO) | Excel Programming | |||
| Import Queries instead of Tables (ADO) | Excel Programming | |||
Hybrid Mode
