Need help: After saved as CSV by using Excel, the column becomes unknown text!
|
|
 Dec 27 2017, 10:39 AM, updated 8y ago
Show posts by this member only | Post
#1
|
 
Junior Member
771 posts Joined: Jan 2009 
|
Hi K forumers, I have a problem with Excel, it's n issue I been facing for many years, I need your help to find out about this!
Can someone enlighten me please! Thanks I have a CSV text file like the below picture: 
After I open the file by using Excel, and save as CSV, that particular column has becomes like this (in the below picture): 
|
|
|
|
|
|
Dec 27 2017, 10:41 AM
Show posts by this member only | Post
#2
|
Senior Member
710 posts Joined: Nov 2012  
|
Not sure if this helps:
https://superuser.com/questions/861964/how-...2-automatically You need to format the column as text then export it to csv. |
|
|
Dec 27 2017, 10:46 AM
Show posts by this member only | Post
#3
|
Senior Member
1,441 posts Joined: Nov 2006 From: I Do Not Know
|
Have you tried opening a blank excel and just importing the csv with the import function.
It even allows you to decide how you wan to segregate the information into each column. |
|
|
Dec 27 2017, 10:50 AM
Show posts by this member only | Post
#4
|
Senior Member
1,421 posts Joined: Nov 2013
|
Chill TS Chill...
try double click the side of the header "B" and it will fit to size of your data. sometimes Excel will shorten the numbers into that format if only limited available size |
|
|
Dec 27 2017, 11:12 AM
Show posts by this member only | Post
#5
|
|
Junior Member
771 posts Joined: Jan 2009
|
QUOTE(SaberCortez @ Dec 27 2017, 09:41 AM) Not sure if this helps: I did, it's still the same, I actually set the column as text before save.https://superuser.com/questions/861964/how-...2-automatically You need to format the column as text then export it to csv.  |
|
|
Dec 27 2017, 10:16 PM
Show posts by this member only | Post
#6
|
|
Senior Member
2,096 posts Joined: Dec 2011
|
QUOTE(k town shit @ Dec 27 2017, 11:12 AM) I did, it's still the same, I actually set the column as text before save. Just set the column type to text again after import |
|
|
Dec 27 2017, 10:21 PM
Show posts by this member only | IPv6 | Post
#7
|
Senior Member
2,734 posts Joined: Mar 2008
|
A trick before converting CSV file into Excel worksheet. Since CSV file is just a normal text file, you can just replace any occurrence of a delimiter with a substitution:
For example: replace delimiter | with |' or replace delimiter , with ,' since Excel will treat any ' in front of a CSV element into a left-justified text. If the CSV file is too big in size, instead of using the standard Windows Notepad, use Notepad++ (available on the Internet) which can handle such text files to perform the substitution. This post has been edited by eclectice: Dec 27 2017, 10:25 PM |
|
|
Dec 28 2017, 12:32 AM
Show posts by this member only | Post
#8
|
Validating
67 posts Joined: Nov 2017
|
https://support.office.com/en-us/article/Im...7b-339e391393ba
Try to use Import Text Wizard. You can then tell Excel what is the delimiter and which column to be formatted as text. QUOTE If the file is a text file (.txt), Excel starts the Import Text Wizard. When you are done with the steps, click Finish to complete the import operation. See: Import data using the Text Import Wizard for more information about delimiters and advanced options. If the file is a .csv file, Excel automatically opens the text file and displays the data in a new workbook. Note: When Excel opens a .csv file, it uses the current default data format settings to interpret how to import each column of data. If you want more flexibility in converting columns to different data formats, you can use the Import Text Wizard. For example, the format of a data column in the .csv file may be MDY, but Excel's default data format is YMD, or you want to convert a column of numbers that contains leading zeros to text so you can preserve the leading zeros. To force Excel to run the Import Text Wizard, you can change the file name extension from .csv to .txt before you open it, or you can Import a text file by connecting to it. Hope this helps. |
| Change to: |  0.0202sec
 0.51
 6 queries
 GZIP Disabled
Time is now: 20th December 2025 - 01:54 AM |
Quote