Converting Multiples of Text files as CSV files with condition

[vdahival]

Hi Friends,

I am new to this group. Need your help to do a task (which very hard for me)

My question as follows.


I have multiples of text files which I need to convert in “csv” file but conditions is that while converting “txt” to “csv” the “csv” file must contains only 14 records (rows) from each text file. If text file contains 140 rows then 14 “csv” files must be generate containing 14 each records each file.

Please help to do this task.


Regards,

Vaibhav

 

[SirJB7]

Hi, vdahival!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


Would you please upload a sample file so as to check field structure? Or they're just records, i.e., one field (column) by record (row)? Give a look at the second green sticky post at this forums main page for uploading guidelines.


Regards!

 

[vdahival]

Thank you sir,

It is just file which contains the sample data as given below

"Abcdef|0000000000000|000000|0|NA|NA|0000000000000000|000000000|00000000|00000000|NA|0000000000|Rocky|NA"

Some files having 14 row or some have rows more than 10000. My concern is that want create a csv file with 14 records each file. with Comma separated value.


Regards,

Vdahival

 

[vdahival]

Please revert

 

[SirJB7]

Hi, vdahival!

A few days ago I started working on another project to adapt it to your requirements. I'm having a couple of weeks absolutely overloaded so I couldn't finish it. Next week, from Tuesday in advance, maybe I'll be with a little more time to go on with this. For the time being, this is the file, unfinished:

https://dl.dropbox.com/u/60558749/Converting%20Multiples%20of%20Text%20files%20as%20CSV%20files%20with%20condition%20%28for%20vdahival%20at%20chandoo.org%29.rar

Regards!

 

[vdahival]

Thank you sir for your reply.

 

[SirJB7]

Hi, vdahival!

I'm sorry but there's a little bit offset, I think that until next Monday.

Regards!

 

[Debraj]

Hi Vaibhav,


Can you please try the below for me..

[pre]

Option Explicit

Sub RaedCSVwithCondition()
Dim FileNames, File
Dim lastrow As Long
FileNames = Application.GetOpenFilename(Filefilter:="TXT Files (*.TXT), *.TXT", MultiSelect:=True)
For Each File In FileNames
With ThisWorkbook.Sheets(1)
lastrow = .Range("A" & .Cells.Rows.Count).End(xlUp).Row
End With
Workbooks.Open File
With ActiveSheet
.Rows("1:14").Copy ThisWorkbook.Sheets(1).Range("A" & lastrow + 1)
.Parent.Close False
End With
Next File
Sheets(1).Copy
ActiveWorkbook.SaveAs ThisWorkbook.Path & "Combined CSV File", xlCSV
ActiveWorkbook.Close True
ThisWorkbook.Sheets(1).Cells.Clear
End Sub

[/pre]

https://dl.dropbox.com/u/78831150/Excel/Converting%20Multiples%20of%20Text%20files%20as%20CSV%20%28vdahival%29.xlsm


Regards,

Deb

 

[vdahival]

Thanks Deb for reverting.

by using your macro it combine all text file in single csv file. Where as i need a macro in which macro can create a csv which contains only 14 records from text file. i.e. if a text file contains 28 rows (record) then there should be 2 csv file containing 14 record each. The value must be comma separated.


Once again thanks DEB.

 

[Debraj]

Hi Vaibhav,

OOPS, I misread the below line..

file must contains only 14 records (rows) from each text file

So, you want to read a Single Text File, and want to split it in Several CSV File..


Please find the updated code..

[pre]

Sub ReadSingleTextFileCreateMultipleCSV()
Dim File
Dim SingleText As Worksheet, MultipleCSV As Worksheet
Dim lastrow As Long, counter As Long
Dim i As Long, j As Long
counter = 1: j = 1
Application.ScreenUpdating = False
File = Application.GetOpenFilename(Filefilter:="TXT Files (*.TXT), *.TXT", MultiSelect:=False)
If File = False Then Exit Sub
Workbooks.Open File
Set SingleText = ActiveSheet
Set MultipleCSV = ThisWorkbook.Sheets("Multiple")
With SingleText
lastrow = .Range("A" & .Cells.Rows.Count).End(xlUp).Row
End With
For i = 1 To lastrow
SingleText.Rows(i).Copy MultipleCSV.Range("A" & counter)
If counter > 14 Then
MultipleCSV.Copy
ActiveWorkbook.SaveAs ThisWorkbook.Path & "CSV File " & j, xlCSV
ActiveWorkbook.Close True
MultipleCSV.Rows("2:20").Delete
counter = 1: j = j + 1
End If
counter = counter + 1
Next i
MultipleCSV.Copy
ActiveWorkbook.SaveAs ThisWorkbook.Path & "CSV File " & j, xlCSV
ActiveWorkbook.Close True
MultipleCSV.Cells.Clear
SingleText.Parent.Close False
Application.ScreenUpdating = True
MsgBox "File has been splitted.."
End Sub

[/pre]

https://dl.dropbox.com/u/78831150/Excel/Converting%20Multiples%20of%20Text%20files%20as%20CSV%20%28vdahival%29%20V2.xlsm


Please confirm, if the same is your requirement.


Regards,

Deb

 

[vdahival]

Thank you once again....

This code is not running as per my requirement. In source folder i have multiple text file.

the code which you have provided is taking only one file at a time. and it not splitting a text file into csv. with keeping 14 records in each csv.


Thanks Deb.