Amanda Walshaw
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
here is a sammple file
excel.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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,1995011 7,20110701 ,19950117, <NULL>,1,F LGEN,GENER AL,FL00,<N ULL>,5814, 100,0,0,"" ,5899,0,1, 0,20110705 ,1,0
0,"AM2010000","221 CHARMAN RD CHELTENHAM",1,0.00,1995011
You should remove the quotes from the excel file before saving as csv. It will automatically insert quotes when saving
ASKER
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"",","""C HELTENHAM DIR REVAL"",","1,","214000,"," 20010731," ,"20110701 ,","200107 31,","<NUL L>,","1,", "FLGEN,"," GENERAL,", "FL00,","< NULL>,","5 814,","100 ,","0,","0 ,",""" "",","5887,","0,","1,","0, ","2011070 1,","1,"," 0,"
"0,","""AM2010002"",","""L AIDLEY CREMATORIUM (LAND)"",","1,","0,","1996 1223,","20 110701,"," 19961223," ,"<NULL>," ,"1,","CLG EN,","GENE RAL,","CL0 0,","<NULL >,","3719, ","100,"," 0,","0,"," "" "",","69,","0,","1,","0,", "20110701, ","1,","0, "
"0,","""AM2010004"",","""B UILDING REFURBISHMENT"",","1,","41 7694.92,", "20090315, ","2011070 1,","20090 315,","<NU LL>,","1," ,"FBIMP,", "GENERAL," ,"FB00,"," <NULL>,"," 3706,","10 0,","0,"," 0,",""" "",","6339,","0,","1,","0, ","2011070 1,","1,"," 0,"
"0,","""AM2010006"",","""S HELVING"", ","1,","29 49.47,","2 0050830,", "20110701, ","2005083 0,","<NULL >,","1,"," FFGEN,","G ENERAL,"," FF00,","<N ULL>,","12 20,","100, ","0,","0, ",""" "",","5555,","0,","1,","0, ","2011070 1,","1,"," 0,"
"0,","""AM2010007"",","""A IR CONDITIONER"",","1,","338. 44,","2005 0501,","20 110701,"," 20050501," ,"<NULL>," ,"1,","PEG EN,","GENE RAL,","PE0 0,","<NULL >,","2602, ","100,"," 0,","0,"," "" "",","5516,","0,","1,","0, ","2011070 1,","1,"," 0,"
"0,","""AM2010011"",","""S ERVER"",", "1,","5078 .77,","200 80207,","2 0110701,", "20080207, ","<NULL>, ","1,","IT HSE,","GEN ERAL,","IT 00,","<NUL L>,","1220 ,","100,", "0,","0,", """ "",","5993,","0,","1,","0, ","2011070 1,","1,"," 0,"
"0,","""AM2010013"",","""C OMPUTER"", ","1,","73 7.62,","20 080512,"," 20110701," ,"20080512 ,","<NULL> ,","1,","I THPC,","GE NERAL,","I T00,","<NU LL>,","122 0,","100," ,"0,","0," ,""" "",","6077,","0,","1,","0, ","2011070 1,","1,"," 0,"
"0,","""AM2010016"",","""I PHONE"",", "1,","729. 13,","2010 1002,","20 110701,"," 20101002," ,"<NULL>," ,"1,","OEM PC,","GENE RAL,","OE0 0,","<NULL >,","1220, ","100,"," 0,","0,"," "" "",","6773,","0,","1,","0, ","2011070 1,","1,"," 0,"
"0,","""AM2010017"",","""P ROJECTOR"" ,","1,","6 08.84,","2 0040701,", "20110701, ","2004070 1,","<NULL >,","1,"," OEGEN,","G ENERAL,"," OE00,","<N ULL>,","12 20,","100, ","0,","0, ",""" "",","5348,","0,","1,","0, ","2011070 1,","1,"," 0,"
,"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"",","""C
"0,","""AM2010002"",","""L
"0,","""AM2010004"",","""B
"0,","""AM2010006"",","""S
"0,","""AM2010007"",","""A
"0,","""AM2010011"",","""S
"0,","""AM2010013"",","""C
"0,","""AM2010016"",","""I
"0,","""AM2010017"",","""P
How about save as in Text (MS DOS)?
ASKER
ok I will try that
ASKER
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,1995011 7,20110701 ,19950117, <NULL>,1,F LGEN,GENER AL,FL00,<N ULL>,5814, 100,0,0,"" ,5899,0,1, 0,20110705 ,1,0
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,1995011
ASKER
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.
Accept the data with the string fields and commas in excel or csv.
simply copy and paste into text all and all okay.
ASKER
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.GetSaveAsFilen ame("", "(*.txt),*.txt", 1)
Open sfn For Output As #1
Print #1, csvs
Close 1
End Sub
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.GetSaveAsFilen
Open sfn For Output As #1
Print #1, csvs
Close 1
End Sub
ASKER