Link to home
Start Free TrialLog in
Avatar of Amanda Walshaw
Amanda WalshawFlag for Australia

asked on

Saving excel file to text comma between each field

I have to import data into an applicatin and the only way it will accep it is in txt field, I can copy and paste into text. how can i input ',' after each field and " " to incdicate string fields.

here is a sammple file

excel.xls
ASKER CERTIFIED SOLUTION
Avatar of tflai
tflai

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Saqib Husain
Saqib Husain
Flag of Pakistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Amanda Walshaw

ASKER

That is fine, but where indicated on the file I need those " " on certain fields not all fields otherwise my import will not work.
here is an entry that is correct for upload, how do I get only certain fields " "

0,"AM2010000","221 CHARMAN RD CHELTENHAM",1,0.00,19950117,20110701,19950117,<NULL>,1,FLGEN,GENERAL,FL00,<NULL>,5814,100,0,0,"",5899,0,1,0,20110705,1,0
You should remove the quotes from the excel file before saving as csv. It will automatically insert quotes when saving
But I only need the quotes around these fields
,"AM2010000","221 CHARMAN RD CHELTENHAM",

when you save as csv and open in notepad it puts the quotes around everything, how do you keep the quotes I need and remove the other ones?

ie


"0,","""AM2010001"",","""CHELTENHAM DIR REVAL"",","1,","214000,","20010731,","20110701,","20010731,","<NULL>,","1,","FLGEN,","GENERAL,","FL00,","<NULL>,","5814,","100,","0,","0,",""" "",","5887,","0,","1,","0,","20110701,","1,","0,"
"0,","""AM2010002"",","""LAIDLEY CREMATORIUM (LAND)"",","1,","0,","19961223,","20110701,","19961223,","<NULL>,","1,","CLGEN,","GENERAL,","CL00,","<NULL>,","3719,","100,","0,","0,",""" "",","69,","0,","1,","0,","20110701,","1,","0,"
"0,","""AM2010004"",","""BUILDING REFURBISHMENT"",","1,","417694.92,","20090315,","20110701,","20090315,","<NULL>,","1,","FBIMP,","GENERAL,","FB00,","<NULL>,","3706,","100,","0,","0,",""" "",","6339,","0,","1,","0,","20110701,","1,","0,"
"0,","""AM2010006"",","""SHELVING"",","1,","2949.47,","20050830,","20110701,","20050830,","<NULL>,","1,","FFGEN,","GENERAL,","FF00,","<NULL>,","1220,","100,","0,","0,",""" "",","5555,","0,","1,","0,","20110701,","1,","0,"
"0,","""AM2010007"",","""AIR CONDITIONER"",","1,","338.44,","20050501,","20110701,","20050501,","<NULL>,","1,","PEGEN,","GENERAL,","PE00,","<NULL>,","2602,","100,","0,","0,",""" "",","5516,","0,","1,","0,","20110701,","1,","0,"
"0,","""AM2010011"",","""SERVER"",","1,","5078.77,","20080207,","20110701,","20080207,","<NULL>,","1,","ITHSE,","GENERAL,","IT00,","<NULL>,","1220,","100,","0,","0,",""" "",","5993,","0,","1,","0,","20110701,","1,","0,"
"0,","""AM2010013"",","""COMPUTER"",","1,","737.62,","20080512,","20110701,","20080512,","<NULL>,","1,","ITHPC,","GENERAL,","IT00,","<NULL>,","1220,","100,","0,","0,",""" "",","6077,","0,","1,","0,","20110701,","1,","0,"
"0,","""AM2010016"",","""IPHONE"",","1,","729.13,","20101002,","20110701,","20101002,","<NULL>,","1,","OEMPC,","GENERAL,","OE00,","<NULL>,","1220,","100,","0,","0,",""" "",","6773,","0,","1,","0,","20110701,","1,","0,"
"0,","""AM2010017"",","""PROJECTOR"",","1,","608.84,","20040701,","20110701,","20040701,","<NULL>,","1,","OEGEN,","GENERAL,","OE00,","<NULL>,","1220,","100,","0,","0,",""" "",","5348,","0,","1,","0,","20110701,","1,","0,"
Avatar of tflai
tflai

How about save as in Text (MS DOS)?
ok I will try that
No that did not work either see result  Book1.txt

0      """AM2010000"""      """221 CHARMAN RD CHELTENHAM"""      """CORP"""      20110701      20110701      1      """NONE"""      $0.00
0      """AM2010001"""      """CHELTENHAM DIR REVAL"""      """CORP"""      20110701      20110701      1      """NONE"""      $0.00
0      """AM2010002"""      """LAIDLEY CREMATORIUM (LAND)"""      """CORP"""      20110701      20110701      1      """NONE"""      $0.00
0      """AM2010004"""      """BUILDING REFURBISHMENT"""      """CORP"""      20110701      20110701      1      """S_005.00"""      $0.00
0      """AM2010006"""      """SHELVING"""      """CORP"""      20110701      20110701      1      """S_040.00"""      $0.00
0      """AM2010007"""      """AIR CONDITIONER"""      """CORP"""      20110701      20110701      1      """S_018.00"""      $0.00
0      """AM2010011"""      """SERVER"""      """CORP"""      20110701      20110701      1      """S_022.00"""      $0.00
0      """AM2010013"""      """COMPUTER"""      """CORP"""      20110701      20110701      1      """S_050.00"""      $0.00
0      """AM2010016"""      """IPHONE"""      """CORP"""      20110701      20110701      1      """S_014.29"""      $0.00
0      """AM2010017"""      """PROJECTOR"""      """CORP"""      20110701      20110701      1      """NONE"""      $0.00
0      """AM2010000"""      """221 CHARMAN RD CHELTENHAM"""      """TAX"""      20110701      20110701      1      """NONE"""      $0.00
0      """AM2010001"""      """CHELTENHAM DIR REVAL"""      """TAX"""      20110701      20110701      1      """NONE"""      $0.00
0      """AM2010002"""      """LAIDLEY CREMATORIUM (LAND)"""      """TAX"""      20110701      20110701      1      """NONE"""      $0.00
0      """AM2010004"""      """BUILDING REFURBISHMENT"""      """TAX"""      20110701      20110701      1      """D_005.00"""      $0.00
0      """AM2010006"""      """SHELVING"""      """TAX"""      20110701      20110701      1      """D_010.00"""      $0.00
0      """AM2010007"""      """AIR CONDITIONER"""      """TAX"""      20110701      20110701      1      """D_010.00"""      $0.00
0      """AM2010011"""      """SERVER"""      """TAX"""      20110701      20110701      1      """D_050.00"""      $0.00
0      """AM2010013"""      """COMPUTER"""      """TAX"""      20110701      20110701      1      """D_050.00"""      $0.00
0      """AM2010016"""      """IPHONE"""      """TAX"""      20110701      20110701      1      """D_030.00"""      $0.00
0      """AM2010017"""      """PROJECTOR"""      """TAX"""      20110701      20110701      1      """D_010.00"""      $0.00


what I need to see is the following

0,"AM2010000","221 CHARMAN RD CHELTENHAM",1,0.00,19950117,20110701,19950117,<NULL>,1,FLGEN,GENERAL,FL00,<NULL>,5814,100,0,0,"",5899,0,1,0,20110705,1,0
I found the answer so easy.

Accept the data with the string fields and commas in excel or csv.
simply copy and paste into text all and all okay.

my example worked
I was working on this while you accepted the answer. You might like to try this macro which can save you a couple of steps.

Sub wcsv()
Dim rw As Range, cel As Range
Dim csvs As String, sfn As String
For Each rw In ActiveSheet.UsedRange.Rows
For Each cel In rw.Cells
csvs = csvs & cel.Value & ","
Next cel
csvs = Left(csvs, Len(csvs) - 1) & vbCrLf
Next rw
sfn = Application.GetSaveAsFilename("", "(*.txt),*.txt", 1)
Open sfn For Output As #1
Print #1, csvs
Close 1
End Sub