Macro to convert TXT to CSV files

[neobavesten]

Hi,

I have a bulk of TXT files organized in same format. Is the a way to write a macro that could convert those text files to *csv files?

Text file contains multiple sentences. I would like to keep that text as one block in CSV file.

I would really appreciate if someone could help me with this.
Many thanks!

 

[neobavesten]

Unfortunately I don't think it picks up all information from the input files.
If you refer back to first page it only reads Title of the file and Article Body.

It does not read "Summary" and "Keywords".

Also article body should be under " ". Since it should be CSV file it should be comma delimited.

 

[diddi]

im gonna call it a night. i hope that you are a lot further down the path with this project. its nearly 3am here, so i'll look in tomorrow and see how you went.

cheers!

 

[neobavesten]

Diddi ,many thanks!

Cheers and good night!!!

 

[neobavesten]

Hi,

Thanks once again for you effort! Code below works almost perfectly, however there is only one more thing I would like to add.
In the case there are line breaks (empty lines) in the "Article Body", output file will insert commas on those places.
I would like "Article Body" to be under one " ", regardless of line breaks.

Hopefully, this can be done? Any help?

Sub DoConvert()
    Application.Cursor = xlWait
    Open "C:\Combined Files.txt" For Output As #2
    For row = 0 To ListBox1.ListCount - 1
        Open SelFolder & ListBox1.List(row) For Input As #1
        res = ""
        While Not EOF(1)
            Line Input #1, sdata
            If Len(sdata) > 18 Then
                res = res & """" & sdata & """" & "," & vbTab
            End If
        Wend
        Print #2, res
        Close #1
    Next row
    Close
    Application.Cursor = xlDefault
End Sub

 

[diddi]

hmm that might take a bit of tinkering. can yopu post a sample

 

[neobavesten]

Sure. Here are inputs as well as output result. I hope you see what the problem is about.

It is not a secret now that the demand for organic cotton clothing is rising 

However, there are also some clever people who are trying to make more 

a) You use the normal/conventional cotton and claim that it was organic. If 

b) You get the organic cotton. Made the fabric from it and dye it like you

"It is not a secret now that the demand for organic cotton clothing is rising quickly.",    "However, there are also some clever people who are trying to make more ",    "a) You use the normal/conventional cotton and claim that it was organic. ",    "b) You get the organic cotton. Made the fabric from it and dye it like you do with the normal cotton.",    "c) You get the organic cotton and dye it with dyestuff that is allowed by the organic certification companies and follow their guidelines.

 

[diddi]

i see the problem, could you post the whole textfile so i can work out a way to tag the article body

 

[neobavesten]

Sample text file is attached below. Now more thing, though, we need to
start each text file in new row (line) in combined file.
So basically, if there are 300 articles, there should be 300 lines as well.
There will be four columns (Title, Summary, Keywords, Article Body) in the combined file. I hope you see my point

Title: 
3 Different Kinds of Organic Tshirts and What to Choose?

Word Count:
279

Summary:
How to prevent from being fooled while choosing organic tshirts!


Keywords:
Organic Cotton, Clothing, Organic, Apparel, T-shirt, Textile, Tshirt, Organic Fabric, Garment, Organic Cotton Clothing, Ecological Clothing, Organic Cotton Fabric


Article Body:
It is not a secret now that the demand for organic cotton clothing is rising quickly. People are willing to buy more natural clothing to themselves to prevent from harmful chemicals that are used at several textile processes.

However, there are also some clever people who are trying to make more money on using this weakness of people. I mean how will you trust that the t-shirt you purchased is really organic? There are 3 common usages of organic cotton.

a) You use the normal/conventional cotton and claim that it was organic. If people believes you then you will make %30 more profit with the exact same garments. So please carefully check the labels of the clothing before you buy and also try to buy from big brands!

b) You get the organic cotton. Made the fabric from it and dye it like you do with the normal cotton. In the end you will have a harmful and chemical fabric which is not organic but "made from organic cotton".

c) You get the organic cotton and dye it with dyestuff that is allowed by the organic certification companies and follow their guidelines. This is costly and the final fabric, t-shirt price will be higher but this is ofcourse healtier. In the end your organic fabric will not be harmful! In that case you can also say the term "organic t-shirt" easily.

Confusing right? Try to stay away from "made in India" and "made in China" organic cotton products for now. USA and Turkey is offering much better quality and trusted business at the moment. I will try to explain the details in my next article on fromorganic.com web site.

 

[diddi]

Sub DoConvert()
    Dim Flag as boolean,res as string,row as long
    Application.Cursor = xlWait
    Open "C:\Combined Files.txt" For Output As #2
    For row = 0 To ListBox1.ListCount - 1
        Open SelFolder & ListBox1.List(row) For Input As #1
        res = "":Flag=false
        While Not EOF(1)
            Line Input #1, sdata
            if left(sdata,13)="Article Body:" then flag =true
                If Len(sdata) > 18 Then
                    if flag=false then
                        res = res & """" & sdata & """" & "," & vbTab
                    else
                        res = res & """" & sdata & """" 
                    end if
                end if
            End If
        Wend
        Print #2, res
        Close #1
    Next row
    Close
    Application.Cursor = xlDefault
End Sub

 

[neobavesten]

Thanks! There seems to be compile error: "End if without block if".
Points to:

                    Else
                        res = res & """" & sdata & """"
                    End If
                End If
            End If     <------!
        Wend