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!

 

[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
        Wend
        Print #2, res
        Close #1
    Next row
    Close
    Application.Cursor = xlDefault
End Sub

 

[neobavesten]

Hm, almost done, but it still puts "" after each black line. Complete output file is attached. We just need to get rid of those extra "" in the article body.

"3 Different Kinds of Organic Tshirts and What to Choose?",    "How to prevent from being fooled while choosing organic tshirts!",    "Organic Cotton, Clothing, Organic, Apparel, T-shirt, Textile, Tshirt, Organic Fabric, Garment, Organic Cotton Clothing, Ecological Clothing, Organic Cotton Fabric",    "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."
"5 Reasons Hale Bob Clothes Are Great!",    "Here you will learn about Hale Bob Clothes, where they originated from, and why they are a great choice as an addition to your wardrobe to give it some originality and spice it up.",    "hale bob clothes, hale bob dress",    "Hale Bob Clothes are excellent, and definitely stand out in the crowd. Hale Bob was created by a guy named Daniel Bohbot who put three important parts of his life together. He took his enjoyment of travelling, his ability to spot beauty, as well as his business experience to make Hale Bob.""Now that you know where Hale Bob Clothes originated from, let's get started with the reasons why they are excellent choice of clothes to add to your wardrobe.""1. Quality. Daniel Bohbot doesn't just design what he likes, he goes into the heart of Los Angeles to pick up ideas for his upcoming pieces, so one can be ensured that his clothing is neither undesired, or outdated. Along with his ability to spot desirable fashion, the quality of Hale Bob is exceptional, and is worth every penny spent.""2. Shock. Daniel realized that there was a demand for unique pieces of clothing that appealed to everyone, yet stood out from the crowd. He refers Hale Bob Clothes not as a staple, but as a spice to a wardrobe. His clothing is very extravagant with great designs and a variety of colors. Daniel's clothes have a definite shock factor to them. ""For example, if you walk into a crowd of people with a Hale Bob Dress on, you'd surprise people with the colors and designs on your dress, as well as how perfect it fits together. Many designers will take colors and patterns, put them together, and the piece of clothing will turn out very unattractive. Daneil Bohbot on the other hand has a great talent to make each and every one of his pieces beautiful.""3. Fashion. Take a look at your gossip magazines or your fashion magazines, you'll see a lot of Hale Bob Clothes in the shopping sections. A position in a very popular magazine like OK!, People, or Star is something that you have to earn, not something your just given. Daniel's clothes are recognized. You know that his clothes are quality, and fashionable.""4. Celebrities. If you were to look at a celebrity of a magazine, you probably aren't thinking to yourself, "I wonder what brand dress that is", now are you? Do a little research and you'll find out that many hot celebrities are wearing Daniel's clothes. A few to name are Eva Longoria, Heidi Klum, Britney Spears, Halle Berry, Sharon Stone, Ashley Tisdale, Paris Hilton, Hayden Panettiere, Cameron Diaz, Salma Hayek, Tori Spelling, and much, much more.""5. Price. Hale Bob Clothes can run anywhere from $100-350. For being an excellent designer, and having clothes that stand out so much, the pricetag is very affordable for those looking.  That price is also retail, a lot of times you'll be able to find ebay prices at exceptionally lower, and attain the same quality. If your looking for affordable clothes then visit http://halebobclothes.com and check out the great selection of clothes and extremely affordable prices.""All in all, Hale Bob Clothes are very fashionable, affordable, and one of a kind. If you want to stand out of the crowd, or simply want clothing that doesn't look like what everyone else is wearing, then you can rely on the quality of Hale Bob."

 

[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
        Wend
        Print #2, res
        Close #1
    Next row
    Close
    Application.Cursor = xlDefault
End Sub

gee have you noticed how many veiws this thread has?

 

[neobavesten]

Hm, now there are no "" around Article body?

 

[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
                res = res & """" 
            end if
            If Len(sdata) > 18 Then
                if flag=false then
                    res = res & """" & sdata & """" & "," & vbTab
                else
                   res = res & sdata       
                end if
            End If
            res = res & """"
        Wend
        Print #2, res
        Close #1
    Next row
    Close
    Application.Cursor = xlDefault
End Sub

 

[neobavesten]

Hm, unfirtunately, this is not a solution... Please take a look at output.

""3 Different Kinds of Organic Tshirts and What to Choose?",    """""""How to prevent from being fooled while choosing organic tshirts!",    """""Organic Cotton, Clothing, Organic, Apparel, T-shirt, Textile, Tshirt, Organic Fabric, Garment, Organic Cotton Clothing, Ecological Clothing, Organic Cotton Fabric",    """""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 long ,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=0
        While Not EOF(1)
            Line Input #1, sdata
            if left(sdata,13)="Article Body:" then flag =1
            If Len(sdata) > 18 Then
                if flag=0 then
                    res = res & """" & sdata & """" & "," & vbTab
                else
                   if flag=1 then
                       flag=2
                       res = res  & """" & sdata
                   else
                       res = res & sdata
                   end if
                end if
            End If
        Wend
        Print #2, res
        Close #1
    Next row
    Close
    Application.Cursor = xlDefault
End Sub

this will prolly end up with the last " missing....

 

[neobavesten]

That's it!!!

You are true VBA champion Diddi! Many thanks for your effort and help! Very much appreciated!

Cheers!!!

 

[diddi]

my pleasure. happy programming....

 

[neobavesten]

Hi,

Thank you for your valuable inputs! Please not that I am still facing some
issues with the code. The problem is that Column 4 containing article text,
sometimes has quotes inside the text.

That confuses macro, which in that case stops at the first quote inside the
article text.

Is there any piece of the code that could be added that would eliminate all
quotation marks inside the Column 4 (Article text) area?

I would really appreciate if you could help me with this!

Many thanks!