Please help me put in these commas! ;-)

[Givan]

Hello,*

It's the first time I'm posting a message here
I tried to find a solution to my problem in the archive here, but I'm afraid I can't find it exactly.

I'm sure this isn't a difficult case for most of the posters here, but for me it is, so any help will be greatly appreciated!

It is like this;

For the weekend I like to fill in a form for a sportstoto, and because I don't have that much time, I have found a piece of software that directs my guesses and puts them into a txt file with a serie of rows like this:

1x221x21
21xx2121
12x2121x

You'll get the idea

This is all fine except the site where I have to upload my rows only accepts the rows in a txt file with commas like this:

1,x,2,2,1,x,2,1
2,1,x,x,2,1,2,1
Etcetera

So now afterwards I have to put all those commas in manually (which would be very timeconsuming) or don't I?*

I'm sure there must be a way or a code to transform the rows into rows with commas, but I don't have a clue to be honest.

So, if anyone knows how to solve this, then please enlighten me!

Greetings Givan

 

[JimSnyder]

You could import them into Excel as a fixed width field file with each field being one character in length. The you could export it from Excel as a .csv or comma separated values file with the comma between each field.

 

[schielrn]

Are these all in one column or are they in separate columns?

If so, you should be able to save the file as a csv, but you won't see them with the commas opening in excel, but if you open in notepad you will see it. If they are all in one cell, you could use VBA or using mconcat which is part of the morefunc add in:

Excel Workbook
	A	B
1	1x221x21	1,x,2,2,1,x,2,1
2	21xx2121	2,1,x,x,2,1,2,1
3	12x2121x	1,2,x,2,1,2,1,x
Sheet2

#VALUE!

Entered with Ctrl+Shift+Enter

This also assumes a cell only contains 8 characters.

Also if they are all in one cell, you can get them in seperate columns by using text to columns and fixed width and then save as a csv.

Hope that helps and post back with any questions.

 

[JimSnyder]

Recording this as a macro:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 3/10/2011 by Jim Snyder
'
'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Working\Excel-VB\Input.txt", Destination:=Range("A1"))
        .Name = "Input"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(1, 1, 1, 1, 1, 1, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    ChDir "C:\Working\Excel-VB"
    ActiveWorkbook.SaveAs Filename:="C:\Working\Excel-VB\output.csv", FileFormat _
        :=xlCSV, CreateBackup:=False
End Sub

 

[Givan]

Hey guys,

Thanks a lot already for the very quick replies!

I will try your solutions later today and will let you know if I succeeded!

Givan

 

[Givan]

hello again,

@ schielrn," you wrote:

"Also if they are all in one cell, you can get them in seperate columns by using text to columns and fixed width and then save as a csv."

yes, they are in one cell, and sofar this seems to be the easiest method, although I only can seem to save the CSV file with the ; character and not the ,

is there any way to solve this then?

I will also try the other options

 

[schielrn]

Not sure why it would be saving with ; instead of , unless it has to do with some regional settings outside of US versions. As I know formulas and other things use ; instead of , in like german versions ans many others.

But unfortunately I am not too familiar with that type of stuff. I would think saving as a csv would automatically use commas since csv stands for comma separated values?

 

[JimSnyder]

Actually, a .csv file can use any character to separate data. The most common one is the 'pipe', or"|". Very few kinds of data include it whereas a last name, first name will have a comma. Determining what isn't in your data usually lets you pick a 'safe' delimiter to separate your data.