| 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? |
|
#9
Posted to microsoft.public.access.modulesdaovba.ado,microsoft.public.excel.programming
|
|||
|
|||
|
Import csv to different tables
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? "Alex Dybenko" wrote: 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? |
|
#10
Posted to microsoft.public.access.modulesdaovba.ado,microsoft.public.excel.programming
|
|||
|
|||
|
Import csv to different tables
Yes.
-- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Angus" wrote in message ... 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? "Alex Dybenko" wrote: 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? |
|
#11
Posted to microsoft.public.access.modulesdaovba.ado,microsoft.public.excel.programming
|
|||
|
|||
|
Import csv to different tables
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" wrote: Yes. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Angus" wrote in message ... 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? "Alex Dybenko" wrote: 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? |
|
#12
Posted to microsoft.public.access.modulesdaovba.ado,microsoft.public.excel.programming
|
|||
|
|||
|
Import csv to different tables
"Angus" wrote in message
... 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! -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) |
|
#13
Posted to microsoft.public.access.modulesdaovba.ado,microsoft.public.excel.programming
|
|||
|
|||
|
Import csv to different tables
"Douglas J. Steele" wrote: "Angus" wrote in message ... 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)? You are right, this is my mistake, I changed it to 151 now it's ok. 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! 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. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) |
|
#14
Posted to microsoft.public.access.modulesdaovba.ado,microsoft.public.excel.programming
|
|||
|
|||
|
Import csv to different tables
"Angus" wrote in message
... 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! 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. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) |
|
#15
Posted to microsoft.public.access.modulesdaovba.ado,microsoft.public.excel.programming
|
|||
|
|||
|
Import csv to different tables
Got it, thanks. It is done now.
"Douglas J. Steele" wrote: "Angus" wrote in message ... 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! 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. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) |
| 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 | |||
Linear Mode
