Converting txt files to CSV files and Related Problem

[ANONYMOUS123456]

Hello Everyone,
I am converting 500 text files to CSV files by just changing the extension from .txt to .csv. The problem is, the the data in the text file is splitted into multiple columns of the first row of csv file. My objective is, when I convert the text file, then all of the data of text file should stay on A1 cell of the resulted CSV file. Can I achieve this objective ?

 

[Jeffrey Mahoney]

Please provide examples of the data in the files and example of how you want it to look in Excel.

 

[ANONYMOUS123456]

Please provide examples of the data in the files and example of how you want it to look in Excel.

I have attached the snapshot of Text file and the required output CSV file.

Snpashot of Text File:

Snapshot 1

​

Snapshot of required CSV file: All the data of text file should stay in A1 cell after conversion of text file to CSV file as should below.

Snapshot 2​

But The problem is, when I convert the text file then the data in the text file is splitted into multiple columns of the CSV file which I don't want. I have attached the snapshot below... The data is splitted from A1 to K1 which is problem and I want to avoid that. I want all of the data of text file into A1 cell after conversion from text file to CSV file as shown in Snapshot 2.

Snapshot 3​

​

 

[wideboydixon]

CSV = Comma Separated Values

Any commas in your text file will start a new column when imported into Excel. If you want to avoid this then I think you need to use a macro like this:

Option Explicit
Public Sub ImportTextWithReference()

Const sourceFolder = "C:\Import\" ' Change to the import folder

Dim nextFile As String
Dim nextRow As Long
Dim fso As Object
Dim ts As Object

Set fso = CreateObject("Scripting.FileSystemObject")

Application.ScreenUpdating = False

nextRow = 0
nextFile = Dir$(sourceFolder & "*.txt") ' Change to the right file extension
Do Until nextFile = ""
    nextRow = nextRow + 1
    Cells(nextRow, 1).Value = nextFile
    Set ts = fso.OpenTextFile(sourceFolder & nextFile)
    Cells(nextRow, 2).Value = ts.ReadAll
    ts.Close
    nextFile = Dir$
Loop

Application.ScreenUpdating = True

End Sub

WBD

 

[mohan.r1980]

Try this

Sub openText()

'Go to tools>>Reference>> tick "Microsoft Scripting Runtime"


Dim fso As Scripting.FileSystemObject
Dim ts As Scripting.TextStream
Dim sText As String


Set fso = New Scripting.FileSystemObject
Set ts = fso.OpenTextFile("Input Path \123.txt")

  sText = ts.ReadAll
  Sheet1.Range("A1") = sText
On Error Resume Next
  ActiveWorkbook.SaveAs Filename:="Output Path\qqqq.csv", FileFormat:=xlCSV, _
        CreateBackup:=False
  ActiveWorkbook.Close
        
End Sub

 

[ANONYMOUS123456]

CSV = Comma Separated Values

Any commas in your text file will start a new column when imported into Excel. If you want to avoid this then I think you need to use a macro like this:

Option Explicit
Public Sub ImportTextWithReference()

Const sourceFolder = "C:\Import\" ' Change to the import folder

Dim nextFile As String
Dim nextRow As Long
Dim fso As Object
Dim ts As Object

Set fso = CreateObject("Scripting.FileSystemObject")

Application.ScreenUpdating = False

nextRow = 0
nextFile = Dir$(sourceFolder & "*.txt") ' Change to the right file extension
Do Until nextFile = ""
    nextRow = nextRow + 1
    Cells(nextRow, 1).Value = nextFile
    Set ts = fso.OpenTextFile(sourceFolder & nextFile)
    Cells(nextRow, 2).Value = ts.ReadAll
    ts.Close
    nextFile = Dir$
Loop

Application.ScreenUpdating = True

End Sub

WBD

Hi,
I created a random excel file. Pressed Alt+F3 and inserted this code as module. Then I specified the path of the folder where .txt files are located. Then I ran the code but nothing happened ? Am I doing any mistakes or may be there is any other problem?

 

[wideboydixon]

Hi,
I created a random excel file. Pressed Alt+F3 and inserted this code as module. Then I specified the path of the folder where .txt files are located. Then I ran the code but nothing happened ? Am I doing any mistakes or may be there is any other problem?

I ran the same code and it worked without issue. Please check:

[1] You've included the trailing backslash at the end of the sourceFolder string
[2] Your files have a .txt extension in that folder

WBD

 

[ANONYMOUS123456]

Try this

Sub openText()

'Go to tools>>Reference>> tick "Microsoft Scripting Runtime"


Dim fso As Scripting.FileSystemObject
Dim ts As Scripting.TextStream
Dim sText As String


Set fso = New Scripting.FileSystemObject
Set ts = fso.OpenTextFile("Input Path \123.txt")

  sText = ts.ReadAll
  Sheet1.Range("A1") = sText
On Error Resume Next
  ActiveWorkbook.SaveAs Filename:="Output Path\qqqq.csv", FileFormat:=xlCSV, _
        CreateBackup:=False
  ActiveWorkbook.Close
        
End Sub

There are two issues:

1) This code is purposed for one txt file at a time. But I have 500 txt files which I want to convert as a bulk into csv files.
2) Running this code for one file: The code is not running. I have attached the snapshot.

 

[ANONYMOUS123456]

I ran the same code and it worked without issue. Please check:

[1] You've included the trailing backslash at the end of the sourceFolder string
[2] Your files have a .txt extension in that folder

WBD

Yes this code is working like a charm. Tons of thanx and stay blessed

 

[Jeffrey Mahoney]

I don't what this says about me, but I've been using this site for years, and I have yet to post a image. Please tell me how. Do I have to have my own website url to post an image?