 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Importing / Linking / August 2005import all data into a field of a table, now what?
I need to create a program that when a user clicks on a button (or anything that is suitable), the program will import data from a text file and put it into appropriate fields of a table. This must be done automatically. I am able to import all lines (i.e., data) from a text file and put them into ONE field of a table. Can you show me how to separate (using VBA) this single field, split the data, and put them in to the appropriate fields of that table, not just all in only one field. Thank you for your help. Curie Sure... if you can tell us how the database will know how to split the field into separate values. If you use a delimiter (such as a comma or a pipe), you can do it by using the Split function to split the values into an array, and then loop through the array's values to write the values into the fields of the table. You need to give us more information and some sample data, and include information about the table and fields where the values are to be written. By the way, is it necessary to put the entire text file's record into a single field? Did you look at TransferText with an import specification to do the import into the fields in one step?  SignatureKen Snell <MS ACCESS MVP> >I need to create a program that when a user clicks on a button (or anything > that is suitable), the program will import data from a text file and put [quoted text clipped - 9 lines] > Thank you for your help. > Curie Hi Expert, There is no delimited in the text file. It is fixed length. I need to extract some use fields from the txt file. I will have to count the characters in the text file and write code correspondingly. Here is an example of the line of the text like: 5324565356577654444546 5325 #0001Y HH3H5A 315854690891221214342113141234344123423432412334 050 I need to extract HH3H5A, the whole second line, and 050 and put them into fields in a table. Can you show me how to do that? For your last question, I really want to import the text file's record into separate field, but it's the DAT file. Access does not recognize the DAT file. I need to convert it to the TXT file first. Microsoft shows me how to convert from DAT to TXT file and import the whole thing into one field in one table :=( > Sure... if you can tell us how the database will know how to split the field > into separate values. If you use a delimiter (such as a comma or a pipe), [quoted text clipped - 8 lines] > single field? Did you look at TransferText with an import specification to > do the import into the fields in one step? I don't know the details about your data (will HH3H5A always be the characters in positions 36 through 41, for example? is the "second line" a line that follows the carriage return and line feed characters, or is it a second line because of "line wrapping"?, etc.), but in general it's fairly straightforward to parse text strings so long as you can define the rules for how to do it. In general, one uses the string parsing functions: Left, Mid, Right, InStr, InStrRev. Let's start with very detailed "rules" for parsing (see above). If you can define them (always at this position, always the first characters after a line feed, etc.), we can assist you. SignatureKen Snell <MS ACCESS MVP> > Hi Expert, > There is no delimited in the text file. It is fixed length. I need to [quoted text clipped - 35 lines] >> to >> do the import into the fields in one step? Yes, they are always in these positions. The "second line" is a second line because of "line wrapping". There is an Microsoft article shows how to convert DAT to TXT using VB. This VB code takes care two things. The first thing is converting DAT to TXT. The second thing is importing the whole data into ONE field of a table. But the ugly part of this second thing is the first line of the txt file becomes the field name :=(. It makes the situationg worse. Thank you, Curie > I don't know the details about your data (will HH3H5A always be the > characters in positions 36 through 41, for example? is the "second line" a [quoted text clipped - 49 lines] > >> to > >> do the import into the fields in one step? Which article are you using for the DAT to TXT conversion? Not sure if that is the best way to do what you want.....is the DAT file essentially the text file but with a DAT extension instead of a TXT extension? Post the details of the table where you want the data to be parsed and written. Are there just the three fields that you've mentioned in that table? Tell us how to exactly parse one "field" of data into the separate details, and we should be able to suggest some ideas for you. SignatureKen Snell <MS ACCESS MVP> > Yes, they are always in these positions. The "second line" is a second > line [quoted text clipped - 77 lines] >> >> to >> >> do the import into the fields in one step? The article are on http://support.microsoft.com/?id=306144. I used the code to change the extension. Yes, the DAT file is the one I have to deal with it (i.e., convert to TXT file) before I can do anything else. Here is the data after I ran the code to import the DAT file in to one field of a table. ----------------------This is just for example------------------------------ 111112222233344444545Y 2322 #1112 Y 123456789021345678900123455667788990012344434544544545467777 066 ----------------------------------------------------------------------------------- The data will line up becoming a single line in a table after importing. I need to extract, for example, 2322, Y, and 066. How can i do that? Thanks, > Which article are you using for the DAT to TXT conversion? Not sure if that > is the best way to do what you want.....is the DAT file essentially the text [quoted text clipped - 4 lines] > table? Tell us how to exactly parse one "field" of data into the separate > details, and we should be able to suggest some ideas for you. So that I'm understanding correctly, there are spaces after the Y character that separate it from the 1 that is on the next line in the posted message (because of line wrapping), and the same for the last 7 on the "second line" and the 0 on the "third line", is this correct? Before we get into parsing, however, because you're using the TransferText action to import that file, we can avoid this entire parsing action entirely if you create an import specification that will parse the text record into separate fields for you, and then you can use that import specification in the TransferText action and the data will be separated for you. You say that the data record is a fixed-width. And it appears that you want to extract three "parts" of the record for your use. Thus, what you really want to do is to split the record into five fields; e.g., from the example that you posted, you want the "2322", the "Y", and the "066" portions only. So, before we begin this process, I need you to do the following: identify the exact character positions that will contain the "2322" value in each record (meaning, the value is in positions 25 throgh 28, or it's in positions 23 through 28, or it's in positions 25 through 29, etc.); then identify the exact character positions that will contain the "Y" value (earlier, you'd mentioned HH3H5A instead of Y?); then the exact character positions that will contain the "066" value. Identify those "windows" and then we'll continue. SignatureKen Snell <MS ACCESS MVP> > The article are on http://support.microsoft.com/?id=306144. I used the > code [quoted text clipped - 24 lines] >> table? Tell us how to exactly parse one "field" of data into the separate >> details, and we should be able to suggest some ideas for you. You correctly identify the issue. The "windows" are following: "2322" is at 25 through 28. "HH3H5A" is at 36-42 "066" is at 93-97. Thank you, Curie > So that I'm understanding correctly, there are spaces after the Y character > that separate it from the 1 that is on the next line in the posted message [quoted text clipped - 21 lines] > > Identify those "windows" and then we'll continue. OK . ... let's set up the import specification first. Make a copy of a DAT file, and change the extension to TXT. We'll use this file to set up the spec. From your database window, click File | Get External Data | Import. Select the "*.txt" option for the file type. Browse to the file that we just renamed. Select it and click Import. When the wizard window opens, be sure that the Fixed Width option is selected. Then click the Advanced button at bottom left. You'll now see a screen that will let you customize the incoming file's format. Delete all the records that you see there for Field information. In the first record, put FieldIgnore1 as the name, Text as the data type, 1 as start, 24 as width, and No as indexed. In the second record, put FieldWant1 as the name, Text as the data type, 25 as start, 4 as width, and No as indexed. In the third record, put FieldIgnore2 as the name, Text as the data type, 29 as start, 7 as width, and No as indexed. In the fourth record, put FieldWant2 as the name, Text as the data type, 36 as start, 7 as width, and No as indexed. In the fifth record, put FieldIgnore3 as the name, Text as the data type, 43 as start, 50 as width, and No as indexed. In the sixth record, put FieldWant2 as the name, Text as the data type, 93 as start, 5 as width, and No as indexed. Click the Save As... button, give the specification a name, e.g., CurieImportSpec, and then save it by clicking the OK button. Click the OK button on the window where you'd entered the field info. Click Next. This window should show you the "breaks" that will parse the data for you. If it looks ok, click Next. This window allows you to select "new table" or "existing table". If you select "new table", a new table will be created using the filename as its name, and the fields will be named as you listed in the specification. If you select "existing table" and then select a table, that table must have the same number of fields that you put in the specification and they must be named the same as you did in the specification. If this is not correct, make the changes in the table and/or specification so that they match (you'll need to cancel the import step to make changes to the table, then you can start the import again and use the Advanced butt to get to the spec window, where you'd click the Specs button in order to select the spec that you'd created earlier). Click Next. This window just "shows" you the spec settings. Click Next. If you selected "new table", this window will let you say whether you want a primary key or not. Make your choice and click Next. If you had selected "existing table", this window shows instead of the "primary key" window, else it is the next window. At this point, we can stop the import process because you've created and saved the import specification. Click Cancel. Now, in your code, where you use TransferText, put the name of the specification (as a text string: "CurieImportSpec") as the second argument of the call to the method: DoCmd.TransferText acImportFixed, "CurieImportSpec", _ "TableName -leave empty if spec creates new table", "PathToFile", False The False at the end of the call tells ACCESS that the first record in the text file does not contain the actual field names. If that record does contain the field names, then use True instead. SignatureKen Snell <MS ACCESS MVP> > You correctly identify the issue. The "windows" are following: > "2322" is at 25 through 28. [quoted text clipped - 41 lines] >> >> Identify those "windows" and then we'll continue. One correction... you will need to specify a table name as the third argument even if you want the import to go to a new table. My typo. OK . ... let's set up the import specification first. Make a copy of a DAT file, and change the extension to TXT. We'll use this file to set up the spec. From your database window, click File | Get External Data | Import. Select the "*.txt" option for the file type. Browse to the file that we just renamed. Select it and click Import. When the wizard window opens, be sure that the Fixed Width option is selected. Then click the Advanced button at bottom left. You'll now see a screen that will let you customize the incoming file's format. Delete all the records that you see there for Field information. In the first record, put FieldIgnore1 as the name, Text as the data type, 1 as start, 24 as width, and No as indexed. In the second record, put FieldWant1 as the name, Text as the data type, 25 as start, 4 as width, and No as indexed. In the third record, put FieldIgnore2 as the name, Text as the data type, 29 as start, 7 as width, and No as indexed. In the fourth record, put FieldWant2 as the name, Text as the data type, 36 as start, 7 as width, and No as indexed. In the fifth record, put FieldIgnore3 as the name, Text as the data type, 43 as start, 50 as width, and No as indexed. In the sixth record, put FieldWant2 as the name, Text as the data type, 93 as start, 5 as width, and No as indexed. Click the Save As... button, give the specification a name, e.g., CurieImportSpec, and then save it by clicking the OK button. Click the OK button on the window where you'd entered the field info. Click Next. This window should show you the "breaks" that will parse the data for you. If it looks ok, click Next. This window allows you to select "new table" or "existing table". If you select "new table", a new table will be created using the filename as its name, and the fields will be named as you listed in the specification. If you select "existing table" and then select a table, that table must have the same number of fields that you put in the specification and they must be named the same as you did in the specification. If this is not correct, make the changes in the table and/or specification so that they match (you'll need to cancel the import step to make changes to the table, then you can start the import again and use the Advanced butt to get to the spec window, where you'd click the Specs button in order to select the spec that you'd created earlier). Click Next. This window just "shows" you the spec settings. Click Next. If you selected "new table", this window will let you say whether you want a primary key or not. Make your choice and click Next. If you had selected "existing table", this window shows instead of the "primary key" window, else it is the next window. At this point, we can stop the import process because you've created and saved the import specification. Click Cancel. Now, in your code, where you use TransferText, put the name of the specification (as a text string: "CurieImportSpec") as the second argument of the call to the method: DoCmd.TransferText acImportFixed, "CurieImportSpec", _ "TableName", "PathToFile", False The False at the end of the call tells ACCESS that the first record in the text file does not contain the actual field names. If that record does contain the field names, then use True instead. SignatureKen Snell <MS ACCESS MVP> >> You correctly identify the issue. The "windows" are following: >> "2322" is at 25 through 28. [quoted text clipped - 42 lines] >>> >>> Identify those "windows" and then we'll continue. Ken, thank you very much for your details instructions. I will follow that and will let you know when I am done. I really appreciate your help. Curie. > OK . ... let's set up the import specification first. > [quoted text clipped - 67 lines] > text file does not contain the actual field names. If that record does > contain the field names, then use True instead. Ken, It works nicely!!! Thanks a lot. You are genius I have another question. When I want to import another text file with delimited commas as following, "1I", "100" "1G", "1100" ----- and I save the import specification, the data in the imported table including the quote and it is not delimited correctly. What should I do in this case? Thank you, Curie > Ken, thank you very much for your details instructions. I will follow that > and will let you know when I am done. > I really appreciate your help. > Curie. In the specification window, you can set the "text qualifier" character. In your case, you'll want to set it to the " character. Also, set the file type to "delimited" (not "fixed width") and set the comma as the delimiter. SignatureKen Snell <MS ACCESS MVP> > Ken, > It works nicely!!! Thanks a lot. You are genius [quoted text clipped - 14 lines] >> I really appreciate your help. >> Curie. Hi Ken, Yes, I did exactly what you said, but the output to the table was strange. The quotes are still there, even the commas are still there and some values are not delimited at all ??? > In the specification window, you can set the "text qualifier" character. In > your case, you'll want to set it to the " character. Also, set the file type [quoted text clipped - 12 lines] > > Thank you, > > Curie You'll need to give me more details about this second text file's data contents (sample records), the table's structure into which it's being imported, and the settngs that you're using in the import specification. SignatureKen Snell <MS ACCESS MVP> > Hi Ken, > Yes, I did exactly what you said, but the output to the table was strange. [quoted text clipped - 20 lines] >> > Thank you, >> > Curie Hi Ken: Here is the second text file (sample) "1I", "100" "1G", "1100" "1E", "20" "1ABCD", "25" ....... These data are imported into two fields. I set the import specifications, i.e., "text qualifier" character is " and the comma as the delimiter. After importing, the table appears as: Field1--Field2 "1I, 100" "1G, 1100" "1E, 20" "1AB ,CD25" I don't know why quote ", comma are still there, and the last value is chopped off and a part of it appears into the field 2. Thanks, Curie > You'll need to give me more details about this second text file's data > contents (sample records), the table's structure into which it's being [quoted text clipped - 5 lines] > > values > > are not delimited at all ??? Your results appear to be what I would expect if your import specification is still set to Fixed Width. Notice how the first field always has just three characters in it? Be sure that it's been changed to Delimited File. SignatureKen Snell <MS ACCESS MVP> > Hi Ken: > Here is the second text file (sample) [quoted text clipped - 28 lines] >> > values >> > are not delimited at all ??? |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.