import all data into a field of a table, now what?

[Curie]

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

 

[Ken Snell [MVP]]

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?

 

[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 :=(

 

[Ken Snell [MVP]]

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.

--

Ken Snell
<MS ACCESS MVP>

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 :=(

 

[Curie]

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
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.

--

Ken Snell
<MS ACCESS MVP>

 

[Ken Snell [MVP]]

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.
--

Ken Snell
<MS ACCESS MVP>

 

[Curie]

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

 

[Ken Snell [MVP]]

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.
--

Ken Snell
<MS ACCESS MVP>

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,

 

[Curie]

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

 

[Ken Snell [MVP]]

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.

--

Ken Snell
<MS ACCESS MVP>

 

[Ken Snell [MVP]]

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.

 

[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.

 

[Curie]

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 Snell [MVP]]

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.

 

[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 ???

 

[Ken Snell [MVP]]

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.

 

[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

 

[Ken Snell [MVP]]

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.


--

Ken Snell
<MS ACCESS MVP>