• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 153
  • Last Modified:

MS Access CSV Text File Export UTF-8 Separator Delimiter Not Exporting Correctly

I am just all confused here but I see what is causing some of my issues but just don't know how to fix them. I need to export a CSV file in UTF-8 Encoding. This file is for products. I have done that BUT.... in the my field [SkuDescr] (Description) if I have a string of text and then place another string of text below the first and so on in the CSV file export it makes several rows for each string of text causing all kinds of misalignments in the file and messing everything up.

[SkuDescr] is short text 255 length

Example in SkuDescr:

Replaces Models:
G547UJ
AE45T6
J7L97
Width 3/4"
Height 1"

Thanks!
0
Dustin Stanley
Asked:
Dustin Stanley
  • 10
  • 7
4 Solutions
 
Jeff DarlingDeveloper AnalystCommented:
Are your quotes escaped?
0
 
Dustin StanleyEntrepreneurAuthor Commented:
I am not exactly new to CSV files but besides simple ones I am not familiar with. What exactly do you mean by escaped Quotes? I thank you for your help.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Here is an example I used NotePad to collect this info:
1599,"Blower Wheel S990 990 Metal","JONE","990","DOES NOT APPLY","3.75 Wide by 2.375 Tall Inches

REPLACES:
S990
777
0118
20134
0290
20118
20134","","","","VJBP5CP","",
1600,"S89852 89852 Vent cover Without Lens","JONE","89852","DOES NOT APPLY",,"","","","TH0A6","",

Open in new window


1599 and 1600 are the individual product SkuIDs. So 1599 and 1600 are two different products.

on 1599   3.75 Wide by 2.375 Tall Inches is where the Skudescr starts and 20134 is where it ends

On 1600 it does not have any if in the Skudescr
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Dustin StanleyEntrepreneurAuthor Commented:
OK When I import my file into where I want and use that specific software it understands and imports it fine but why is it not showing fine in Excel or CSVed (Software) when I try to just view it before trying to import it where I truly need it?

I need a way to Preview it correctly before importing it where I need. Especially when there are 1000's of records.
0
 
aikimarkCommented:
One problem is that you have a comma at the end of your 1599 record, which may 'trick' the consuming CSV code that the 1600 data is just  part of the 1599 record.  This is a problem caused when you have imbedded Cr or Lf characters in a CSV field.

Escaped quotes could be a pair of quotes.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
I do have Carriage Returns in the database and I truly need them in the SkuDescription. How can I work around this without stripping them away or such?  When there is no carriage return it just looks like a big MumboJumbo Mess!
0
 
aikimarkCommented:
Rather than Notepad, try using Notepad++ and look at all the characters.  Pasting text from the clipboard can change your data, so it would be helpful if you uploaded a text file.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Notepad++ it looks the same also. I thought Notepad would not cause copy paste issues like most software?
0
 
aikimarkCommented:
You can go the regex route and substitute \r or \n or \r\n in place of the Cr Lf or CrLf sequences in the field.  If you have CrLf in the field, try using one of the single character alternatives.  Excel can usually read and render that.
0
 
aikimarkCommented:
are you viewing all the characters (both invisible and visible)?
0
 
aikimarkCommented:
Since you used imbeded (field content) quotes in your question, please include that in your posted data examples.  Also, please use the CODE snippet link above the comment box.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Oh by the way 1599 should have a comma after it. SkuID is 1599 then SkuNm is Blower Wheel S990 990 Metal.

I am exactly set on Excel but I just need a way to preview it properly before saying ok take off and a whole day is wasted when a import has gone wrong.

I appreciate the help!

are you viewing all the characters (both invisible and visible)?
Not sure what you mean so probably not.

Also, please use the CODE snippet link above the comment box.
Sorry
0
 
Dustin StanleyEntrepreneurAuthor Commented:
are you viewing all the characters (both invisible and visible)?
In Notepad++ I am now thanks
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Notepad++ with all characters will help me weed this problem out. Thanks!
0
 
aikimarkCommented:
You misunderstood me.  The extra comma I referenced is at the end of the 1599 LINE, not immediately after the "1599" characters in the first line.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Ok I see what you are saying. In Notepad++ it shows me a CRLF at the end now.  On CSV files to go to a new line is there supposed to always be a CRLF? I just thought it counted the headers and knew when to return to the next line.
0
 
aikimarkCommented:
Generally, in a Windows environment, each line should end in a CrLf sequence.  There is no guarantee that the number of fields in each record/line will match the number of fields in the header.
0
 
Dustin StanleyEntrepreneurAuthor Commented:
Great to know! Thanks for the help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 10
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now