×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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?

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

"a delimited text file" I would assume a comma delimited text file ponder
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.

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

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

(OP)
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.

Quote:


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/getfile.aspx?folder=...

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

Can you upload the text file?

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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


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


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

I did notice this at row 633:634

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


However, in the Text file...

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

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

???

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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

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

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?


---- Andy

There is a great need for a sarcasm font.

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

(OP)

Quote (Andrzejek )

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

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

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

613|STRONG AND JONES FUNERAL HOME INC|........
d Main|PN1|||


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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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" ponder


---- Andy

There is a great need for a sarcasm font.

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

(OP)

Quote (Andrzejek)

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

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

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

"My lips are sealed" - Go it...wink


---- Andy

There is a great need for a sarcasm font.

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

recno 613

in column Viol1Code, which has only BLANKS of PN1 as values has this for RECNO 613...

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.


these are the last 4 columns in your text file...

Viol1Code Viol2Code Viol3Code Viol4Code


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




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


Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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

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

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

(OP)
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? ponder

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close