Problems with the text file generated by exporting Access 2013 to a delimited text file

[weberm]

I am attempting to export a somewhat large Access 2013 table to a delimited text file, with the intention of using the file to load an ORACLE table, but sometimes, it acts like there was a "data hiccup" of some sort that causes a rows to appear to be left or right shifted. I thought perhaps it was caused by a field containing the delimiter and throwing things off-kilter but the rows after it are fine. Further investigation seems to indicate there was an EoL char in the previous row, making it split a row in the table into two records in the data file. I discovered that if I edit the file in notepad and remove the line break, it usually fixes the row, but not always. Odder yet, this behavior isn't consistent. Is anyone aware of what would cause this to happen?

 

[Andrzejek]

a delimited text file" I would assume a comma delimited text file
If so, could you elaborate on "rows to appear to be left or right shifted"? What does that mean?


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Can the delimiter to use be selected? If so, what happens if you select another delimiter, ideally, a character not used anywhere in your table?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[weberm]

I'm using pipe ("|") as a delimiter b/c the name field contains commas. I had written a PL/SQL script to read the file and insert the records in an ORACLE table but didn't want to deal with processing commas and quotes.

If so, could you elaborate on "rows to appear to be left or right shifted"? What does that mean?

Basically, it acts like it encountered the delimiter in the previous row and split the record into two rows in the text file. It's that the row following it are fine. Attached is the text file in a spreadsheet. The pink rows have problems.

https://files.engineering.com/getfi...44f7-a40d-8c5a47dbe444&file=tbCitationPN.xlsx

 

[SkipVought]

Can you upload the text file?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[weberm]

Here is the text file. The problem rows start with 608 and 613.

https://files.engineering.com/getfi...-44cc-9329-f65c13e8051d&file=tbCitationPN.txt

 

[SkipVought]

Here's what my rows 608:613 looks like for the first 3 columns

[tt]
584 ORANGE CITY LEASING INC 220 E NEW YORK AVE
585 H & P MEMORIALS INC 2701 LEE BLVD
586 MERRITT FUNERAL HOME INC 2 SOUTH LEMON AVENUE
587 FRASER LLC 8168 NORMANDY BOULEVARD
588 COLLISONS HOLDING COMPANY 3806 HOWELL BRANCH ROAD
589 ORANGE CITY LEASING INC 220 E NEW YORK AVE
[/tt]

BTW my method: IMPORTED via Data > Get External Data > From Text.

I did notice this at row 633:634
[tt]
608 GUTTERMAN'S INC 7240 N FEDERAL HIGHWAY
Citation initiated, late remittance, re CY 2013. 400 Yes
[/tt]

However, in the Text file...

[pre]
608|GUTTERMAN'S INC|7240 N FEDERAL HIGHWAY|
Citation initiated, late remittance, re CY 2013.|400|Yes|
[/pre]

So at least in this instance, the Excel file is reflecting only an apparent anomaly in the text file.

???


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

This seems to be one of access export limitations - if the length of the line exceeds 1024 characters it is broken into two (or more) lines.
I don't know if you can import so long text to ORACLE. If so, you may try to process the text by code.
If not, split the table using two queries and export queries.
Alternative: try xml format if can be inported to ORACLE.,

combo

 

[Andrzejek]

In Access, RecNo 608, in [tt]Notes[/tt] field, does the text "Citation initiated, late remittance, re CY 2013." start with the Chr(10) or Chr(13) or carriage return?


---- Andy

There is a great need for a sarcasm font.

 

[weberm]

In Access, RecNo 608, in Notes field, does the text "Citation initiated, late remittance, re CY 2013." start with the Chr(10) or Chr(13) or carriage return?

It doesn't appear to be, but I noticed when I exported the table to Excel, it does begin with carriage return for RecNo 608, so that one is being split into two lines.

It appears that RecNo 613 and 614 are being combined into one record...

 

[SkipVought]

613 breaks here in the text file. I copied from the end of rec 613 to the next line in the text file, and pasted that string here...
Preneed Main|PN1|||
[/tt]

In the text file it looks like this...
[tt]
613|STRONG AND JONES FUNERAL HOME INC|........
d Main|PN1|||
[/tt]

So the break is not caused by a control character. It seems that there's a MAX WIDTH in the text file wrapping the text.


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

Looks like sometimes you have missing carriage return, like between RecNo 613 and 614, and sometimes you have extra carriage return, like in Notes field in RecNo 608

And please tell me your text file is NOT from one "large Access 2013 table" and you are NOT going "to load [all of this data into one large] ORACLE table"


---- Andy

There is a great need for a sarcasm font.

 

[weberm]

And please tell me your text file is NOT from one "large Access 2013 table" and you are NOT going "to load [all of this data into one large] ORACLE table"

My lips are sealed. Let's just say it's a very bad idea to teach non-IT customers how to make MS Access apps.

I'm making archive tables that we can reference after we migrate this data into their existing licensing application.

 

[Andrzejek]

My lips are sealed" - Go it...


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

recno 613

in column Viol1Code, which has only BLANKS of PN1 as values has this for RECNO 613...
[tt]
LATE PRENEED REMITTANCE. You were late in remitting for 4th Quarter (Oct 1 - Dec 31) 2013. Your remittance was due on , but was received on . This violates s. 497.453(6), Fla. Statutes. Board Rule 69K-11.003(2)(a) 11 and 12, specify a penalty of $200 for a first time violation and $400 for each subsequent violation.
[/tt]

these are the last 4 columns in your text file...
[tt]
Viol1Code Viol2Code Viol3Code Viol4Code
[/tt]

However, following this column is the following, ONE ROW PER COLUMN (including empty cells) in order to fit in this window, 14 cells in all...
[tt]
[highlight #FCE94F]


Deleted error - Citation initiated, late remittance, re CY 2013 - wrong CY remittance information applied.

200
Err


4th Quarter (Oct 1 - Dec 31)
2013
2/28/2014 0:00
9/16/2014 0:00
Preneed Main
PN1
[/highlight]
[/tt]


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[combo]

As I posted above, this looks like pure access export limitation issue. Before sending my ansewer I did a quick test. I created access table with several short text fields. The table was filled wtth data with ~200 characters in each field. After export to text file, records that had more than 1024 characters (with delimiter) were divided into two lines. There were no line breaks or any special characters in the test table (anyway, this is another topic). You can verify this behaviour in your data: if the record is splitted, the length of the first part is exactly 1024 characters (or CR/LF in the record).
Due to both issues, try to use xml format indstead, it's more solid.

combo

 

[weberm]

Better late than never, but there was a hard return at the beginning of the Notes field in RecNo 608. When we removed it from the field, that record ceased being split and rows 613 and 614 stopped getting concatenated. Weird, huh?