convert a csv file to txt file

[mlabac]

I need to convert a csv file to a tab delimited txt file. Renaming the file does not work.

Thoughts?

Thanks

 

[trollacious]

CSV stands for Comma Separated Values. You'll need to replace the comma separators with tabs, but watch out for commas inside strings surrounded by quotation marks.

Lee

 

[mlabac]

Sorry...should have giving more detail.

I can open the file in excel and save as a txt file...but I need to do this automatically through VB.

Thanks for the quick reply!

 

[snakeeyes1978]

Like he said:

>You'll need to replace the comma separators with tabs, but >watch out for commas inside strings surrounded by >quotation marks.

If you are using VB, try opening the CSV file (either using random access or sequential but sequential is probably preferable) and using the replace command to swap commas for tabs and then rewrite the output to another filename.

 

[trollacious]

If you know how many fields you have, and what the data types are, you can read from the file sequentially and use (from the VB6 help files):

Input #filenumber, varlist

where varlist is the list of variables that are contained on one line. Then open the file to write to, concatenate the list of variables with vbTabKey, and Print them to the file.

Lee

 

[mlabac]

how do i open the csv file?

 

[trollacious]

You gotta at least look up SOME of the stuff yourself.

Lee

 

[mlabac]

Ok, basically i am trying to create a DTS package in SQL server to import this csv file. When I try to import it it can not delimit the fields unless i open the file and save as csv leaving out any incompatible features.

Since i have to open and save as i would prefer that it be in a tab delimited txt file.

I am using the ShellExecute(hWnd, vbNullString, filename, vbNullString, vbNullString, vbNormalFocus) to open the file.

Since I can now open the csv file. Do you know of a way to call the save as command in excel to save the file? If so, would that be easier than using the Input #filenumber, varlist.

Sorry, it has been awhile since I have coded in VB

Thanks for your help

 

[mlabac]

Got it figured out...

Dim myExcelApplication As Excel.Application
Dim myExcelWorkbook As Excel.Workbook
Dim myExcelWorksheet As Excel.Worksheet
Dim myExcelChart As Excel.Chart


Set myExcelApplication = CreateObject("Excel.Application")
Set myExcelWorkbook = myExcelApplication.Workbooks.Open("file.csv")

myExcelWorkbook.SaveAs "file.txt"

myExcelWorkbook.Close True
myExcelApplication.Quit

Thanks for your help

 

[CubeE101]

just incase you are still interested...

This could be as simple as...

Dim FileText As string
Open "file.csv" for input as #1
  FileText = Input(LOF(1), #1)
Close
Open "file.txt" for output as #1
  Print #1, Replace(FileText, ",", vbTab)
Close

Or even:

Open "file.csv" for input as #1
Open "file.txt" for output as #2
  Print #2, Replace(Input(LOF(1), #1), ",", vbTab)
Close

But that is if, as stated above, there are no commas in the actual text of the document...

Your excel code could be simplified as well...

    Dim myExcelApplication As Excel.Application
    Set myExcelApplication = CreateObject("Excel.Application")
    With myExcelApplication
      .DisplayAlerts = False
      .Workbooks.Open "file.csv"
      .ActiveWorkbook.SaveAs "file.txt"
      .Quit
    End With

And just for fun ;-)

With CreateObject("Excel.Application")
  .DisplayAlerts = False
  .Workbooks.Open "file.csv"
  .ActiveWorkbook.SaveAs "file.txt"
  .Quit
End With

BTW... Just as a personal preference, I typically use xlApp for the application object...

Visit My Site
PROGRAMMER: Red-eyed, mumbling mammal capable of conversing with inanimate objects.

 

[HunterW]

I recorded a VB macro in excel this might help all the classes are available to VB so you should be able to do it without excel.

Sub csvtotxt()
'
' csvtotxt Macro
' Macro recorded by Hwylie
'

'
Workbooks.Open Filename:="E:\TA\Directs.xls" '< ActiveWorkbook.SaveAs Filename:="E:\TA\Directs.txt", FileFormat:=xlText, _
CreateBackup:=False
End Sub