PLEASE HELP: Columns not sorting properly when I import from text

  • Thread starter Thread starter sam
  • Start date Start date
S

sam

Hi All,

I want to transfer tables from text file to access. I am able to do this by
using the DoCmd.TransferText command, But I am having issues with missing
values in the text file. What I mean is, in the text file, there are missing
values in many rows and columns, and this is giving issues with sorting the
data in database into different columns. There are 6 columns in text file,
But in access the data is transfered in just two columns.

How can I resolve this?

Thanks in advance
 
It's kind of hard to see what you are talking about. Can you post a few
sample lines of the text file that do not import properly? Then show us how
it was imported.
 
Hi Arvin,

Here are a few rows from the text file:

Acct AcctName Center Officer OfficerName RiskCode
00000001003 WILLIAMM 704 79 PEG NAN 3
00000001012 CHRIS 710 79 PEG NAN 3
00000001185 CHARLES 705 79 PEG NAN 3

How it looks in access after i use the transfertext command:

Acct_AcctName_Center_Officer_OfficerName_RiskCode
00000001003[?]WILLIAMM[?]704 79[?]PEG NAN [?]3

And so on with other rows, all this data is displayed in a single column
seperated with question marks [?]:

Acct_AcctName_Center_Officer_OfficerName_RiskCode

here is my command to transfer the table from text file (Notepad) to access
2007:

DoCmd.TransferText acImportDelim, , "Test1", "C:\REPORTS\Reports 1145\New
Folder\test1.txt", True
 
They don't appear to be delimited. This would be delimited:

Acct AcctName Center Officer OfficerName
RiskCode
00000001003 WILLIAMM 704 79 PEG NAN 3
00000001012 CHRIS 710 79 PEG NAN 3
00000001185 CHARLES 705 79 PEG NAN 3


I hope the newsreader doesn't screw that up so that you can see how the
columns in a delimited file work. The above is how they usually work. Each
field has a specific column width like Acct will always be 11 characters
wide, others have a field width set so if only 8 characters used in a 12
character field, there will be 4 spaces, the next row may have 5 characters,
so there will be 7 spaces.

Some delimited files use a certain character, often the Tab character
Chr(9), to delimit each field. What's happening in your case is that ther is
no defined delimiter, so the code is guessing and getting it wrong, since it
only finds 5 of the 6 fields.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


sam said:
Hi Arvin,

Here are a few rows from the text file:

Acct AcctName Center Officer OfficerName RiskCode
00000001003 WILLIAMM 704 79 PEG NAN 3
00000001012 CHRIS 710 79 PEG NAN 3
00000001185 CHARLES 705 79 PEG NAN 3

How it looks in access after i use the transfertext command:

Acct_AcctName_Center_Officer_OfficerName_RiskCode
00000001003[?]WILLIAMM[?]704 79[?]PEG NAN [?]3

And so on with other rows, all this data is displayed in a single column
seperated with question marks [?]:

Acct_AcctName_Center_Officer_OfficerName_RiskCode

here is my command to transfer the table from text file (Notepad) to
access
2007:

DoCmd.TransferText acImportDelim, , "Test1", "C:\REPORTS\Reports 1145\New
Folder\test1.txt", True




Arvin Meyer said:
It's kind of hard to see what you are talking about. Can you post a few
sample lines of the text file that do not import properly? Then show us
how
it was imported.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access





.
Click to expand...
Click to expand...
 
Actually, that's fixed width, not delimited.

Sam's sample may well be delimited, but with a delimiter that Access doesn't
recognize.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele
(no e-mails, please!)



Arvin Meyer said:
They don't appear to be delimited. This would be delimited:

Acct AcctName Center Officer OfficerName
RiskCode
00000001003 WILLIAMM 704 79 PEG NAN 3
00000001012 CHRIS 710 79 PEG NAN 3
00000001185 CHARLES 705 79 PEG NAN 3


I hope the newsreader doesn't screw that up so that you can see how the
columns in a delimited file work. The above is how they usually work. Each
field has a specific column width like Acct will always be 11 characters
wide, others have a field width set so if only 8 characters used in a 12
character field, there will be 4 spaces, the next row may have 5
characters, so there will be 7 spaces.

Some delimited files use a certain character, often the Tab character
Chr(9), to delimit each field. What's happening in your case is that ther
is no defined delimiter, so the code is guessing and getting it wrong,
since it only finds 5 of the 6 fields.

sam said:
Hi Arvin,

Here are a few rows from the text file:

Acct AcctName Center Officer OfficerName RiskCode
00000001003 WILLIAMM 704 79 PEG NAN 3
00000001012 CHRIS 710 79 PEG NAN 3
00000001185 CHARLES 705 79 PEG NAN 3

How it looks in access after i use the transfertext command:

Acct_AcctName_Center_Officer_OfficerName_RiskCode
00000001003[?]WILLIAMM[?]704 79[?]PEG NAN [?]3

And so on with other rows, all this data is displayed in a single column
seperated with question marks [?]:

Acct_AcctName_Center_Officer_OfficerName_RiskCode

here is my command to transfer the table from text file (Notepad) to
access
2007:

DoCmd.TransferText acImportDelim, , "Test1", "C:\REPORTS\Reports 1145\New
Folder\test1.txt", True




Arvin Meyer said:
It's kind of hard to see what you are talking about. Can you post a few
sample lines of the text file that do not import properly? Then show us
how
it was imported.

Hi All,

I want to transfer tables from text file to access. I am able to do
this
by
using the DoCmd.TransferText command, But I am having issues with
missing
values in the text file. What I mean is, in the text file, there are
missing
values in many rows and columns, and this is giving issues with
sorting
the
data in database into different columns. There are 6 columns in text
file,
But in access the data is transfered in just two columns.

How can I resolve this?

Thanks in advance


.
Click to expand...
Click to expand...
Click to expand...
 
You are correct, I grouped both fixed width and delimited together. His
sample could be either, but it looks fixed width.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access


Douglas J. Steele said:
Actually, that's fixed width, not delimited.

Sam's sample may well be delimited, but with a delimiter that Access
doesn't recognize.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele
(no e-mails, please!)



Arvin Meyer said:
They don't appear to be delimited. This would be delimited:

Acct AcctName Center Officer OfficerName
RiskCode
00000001003 WILLIAMM 704 79 PEG NAN 3
00000001012 CHRIS 710 79 PEG NAN 3
00000001185 CHARLES 705 79 PEG NAN 3


I hope the newsreader doesn't screw that up so that you can see how the
columns in a delimited file work. The above is how they usually work.
Each field has a specific column width like Acct will always be 11
characters wide, others have a field width set so if only 8 characters
used in a 12 character field, there will be 4 spaces, the next row may
have 5 characters, so there will be 7 spaces.

Some delimited files use a certain character, often the Tab character
Chr(9), to delimit each field. What's happening in your case is that ther
is no defined delimiter, so the code is guessing and getting it wrong,
since it only finds 5 of the 6 fields.

sam said:
Hi Arvin,

Here are a few rows from the text file:

Acct AcctName Center Officer OfficerName RiskCode
00000001003 WILLIAMM 704 79 PEG NAN 3
00000001012 CHRIS 710 79 PEG NAN 3
00000001185 CHARLES 705 79 PEG NAN 3

How it looks in access after i use the transfertext command:

Acct_AcctName_Center_Officer_OfficerName_RiskCode
00000001003[?]WILLIAMM[?]704 79[?]PEG NAN [?]3

And so on with other rows, all this data is displayed in a single column
seperated with question marks [?]:

Acct_AcctName_Center_Officer_OfficerName_RiskCode

here is my command to transfer the table from text file (Notepad) to
access
2007:

DoCmd.TransferText acImportDelim, , "Test1", "C:\REPORTS\Reports
1145\New
Folder\test1.txt", True




:

It's kind of hard to see what you are talking about. Can you post a few
sample lines of the text file that do not import properly? Then show us
how
it was imported.

Hi All,

I want to transfer tables from text file to access. I am able to do
this
by
using the DoCmd.TransferText command, But I am having issues with
missing
values in the text file. What I mean is, in the text file, there are
missing
values in many rows and columns, and this is giving issues with
sorting
the
data in database into different columns. There are 6 columns in text
file,
But in access the data is transfered in just two columns.

How can I resolve this?

Thanks in advance


.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top