Solved: csv file problem

[austenr]

is there a way in office 2007 to have the fields in a csv fle enclosed in ""? I seem to recall being able to do this in 2003 but can't quite seem to get it to work in 2007. learning curve :banghead: if you can do it with a macro that would be ideal. i've tried to save it every way I can think of with no luck :confused4

[mperrah]

Are you openning a csv in excel and are trying to remove the ""
or have an excel file and trying to export in csv and need to add the "" ?

[mperrah]

If adding you could try doing a for each statement
and concantinate a " prefix then "&" the cell contents then "&" the suffix "

I'm sure one of the moderators could whip out a code to perform this.
Removing the "" you could find and replace,
Or I found a thread that had me place a 1 in a cell, then copy the cell and paste special in the cells with the "" values and choose multiply.

Hope this helps.
Mark

[austenr]

No, what I was looking for I found.
Thanks.

[RonMcK]

No, what I was looking for I found.
Thanks.

And, what did you find? I can see needing to do the same thing from time to time.

Thanks,
Ron
:)

[austenr]

Let me post the csv file and in addition I will tell you what I am trying to accomplish.

The attached file needs to be formatted correctly so that it will upload into mySQL. Currently it will not. When I can get it to upload, it puts all of the data in one field. Instead of breaking up the record into the individual fields. So it is putting it in like this:

every,good,coder,gets,stuck,now,and,then

is imported into one field instead of 8 fields. I have tried double quotes around each word but that doesnt work either.

[RonMcK]

Is this worksheet the result of your solution or is it the 'source' of your problem and the issue has yet to be solved?

Ron

[austenr]

it is the maim issue i need to resolve this before I can move forward.

[RonMcK]

Would it help to change the Number Type from General to Text for all cells in the table before you attempt to write each field with surrounding quote marks?

[austenr]

What difference does that make?

[RonMcK]

Here is something that might work for you:


Sub MakeQuotedCSV()

Dim Row As Integer, Col As Integer, FirstRow As Integer, LastRow As Integer, FirstCol As Integer, LastCol As Integer

FirstRow = 1
FirstCol = 1
LastRow = GetLastRow
LastCol = GetLastCol

Workbooks("1.csv").Activate
Sheets("1.csv").Activate
Row = FirstRow
While Row <= LastRow
Col = FirstCol
While Col <= LastCol
' Debug.Print Cells(Row, Col).Value
Cells(Row, Col).Value = Chr(34) & Cells(Row, Col).Value & Chr(34)
' Debug.Print Cells(Row, Col).Value
Col = Col + 1
Wend
Row = Row + 1
Wend

Workbooks("1.csv").Save

End Sub

Public Function GetLastRow() As Integer
GetLastRow = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByRows).Row
End Function
Public Function GetLastCol() As Integer
GetLastCol = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
End Function

Let me know if this helps you.

Ron
Orlando, FL:hi:

[austenr]

Thanks. I'll give it a try.

[RonMcK]

Here's your file 'as processed'.

Ron

[RonMcK]

We'll try this, one more time. :banghead:

[austenr]

Great. Thanks