1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

txt files to csv

Discussion in 'Cloaking and Content Generators' started by eddyman1us, Dec 1, 2009.

  1. eddyman1us

    eddyman1us Junior Member

    Joined:
    Jan 31, 2008
    Messages:
    121
    Likes Received:
    8
    Hi , I have directory full of .txt files all articles , is there any way to batch import all the files to a .csv database .
     
    Last edited: Dec 1, 2009
  2. c0nan

    c0nan Junior Member

    Joined:
    Oct 29, 2009
    Messages:
    176
    Likes Received:
    139
    Occupation:
    Java J2EE Programmer
    Location:
    South Africa
    If you have MS Excell, you could do this with some VBA code.

    Here is a link to something similar :
    (I would have writen this for you, but I have recently switched to Ubuntu and OpenOffice, and I am still learning OpenOffice Macros.)

    Code:
    http://hiderefer.org/?http://www.exceluser.com/explore/questions/vba_textcols.htm
    
     
  3. c0nan

    c0nan Junior Member

    Joined:
    Oct 29, 2009
    Messages:
    176
    Likes Received:
    139
    Occupation:
    Java J2EE Programmer
    Location:
    South Africa
    Ok, I have Tried To amend the Code as per your requirements.
    I have NOT Tested It, but it Should Work. I will Test I ASAP, when I get to a Windows Machine.
    Maby I will load a VM on my ubuntu.

    The Result should be like this:
    ____________________________________________________________________
    |___A______________________________________B________________________
    |Testfile.txt|Sample TextSample TextSample TextSample TextSample TextSample Text
    ____________________________________________________________________

    Code:
    ''Require that all variables be declared
    Option Explicit
    
    ''======================================================
    '' Program:    ParseText
    '' Desc:       Reads a text file into a variable then
    ''             writes it into a row, n chars at a time
    '' Called by:  user
    '' Call:
    '' Arguments:
    '' Comments:   Written quickly. No error-checking.
    '' Changes----------------------------------------------
    '' Date        Programmer     Change
    '' 6/14/06     Charley Kyd    Written
    ''======================================================
    Sub ParseText()
    
       ''###C0NAN### - New
       Dim MyFileSystemObject as Object
       Dim MyFolderObject As Object
       Dim MyFileObject As Object
       Dim MyFileCollection As Object
       Dim FolderContainingRawFiles as String
       ''###C0NAN### - New
    
       Dim sText As String, sFile As String
    
       ''Get the full path to the source file
    ''   With ThisWorkbook
    ''      sFile = .Names("SourcePath").RefersToRange
    ''      If Left(sFile, 1) <> "\" Then sFile = sFile & "\"
    ''      sFile = sFile & .Names("SourceFile").RefersToRange
    ''   End With
    
    
       ''###C0NAN###
       FolderContainingRawFiles = "C:\FolderWithFiles"
    
       Set MyFileSystemObject = CreateObject("Scripting.FileSystemObject")
       Set MyFolderObject = MyFileSystemObject.GetFolder(FolderContainingRawFiles)
       Set MyFileCollection = MyFolderObject.Files
       For Each MyFileObject In MyFileCollection
          sFile = MyFileObject.Name
          sText = GetText(sFile)
       next
       ''###C0NAN###
    
    
    
       ''###C0NAN### - Moved into above For Loop
       ''Get the full text string from the text file
       ''sText = GetText(sFile)
       ''###C0NAN### - Moved into above For Loop
    
       ''Remove all nonprintable characters from the text
       ''Comment out if the characters are wanted
       sText = Excel.WorksheetFunction.Clean(sText)
    
       ''Write to the workbook
       WriteToSheet sText,sFile
    End Sub
    
    ''======================================================
    '' Program:    GetText
    '' Desc:       Read a text file into a string and then
    ''             return the string
    '' Called by:  ParseText
    '' Call:       GetText(sFile)
    '' Arguments:  sFile--The full path to the text file
    '' Comments:
    '' Changes----------------------------------------------
    '' Date      Programmer   Change
    '' 6/14/06   Charley Kyd  Written
    ''======================================================
    Function GetText(sFile As String) As String
       Dim nSourceFile As Integer, sText As String
    
       ''Close any open text files
       Close
    
       ''Get the number of the next free text file
       nSourceFile = FreeFile
    
       ''Write the entire file to sText
       Open sFile For Input As #nSourceFile
       sText = Input$(LOF(1), 1)
       Close
    
       GetText = sText
    End Function
    
    ''======================================================
    '' Program:    WriteToSheet
    '' Desc:       Writes a text string to one row of a
    ''             worksheet, n characters per column
    '' Called by:  ParseText
    '' Call:       WriteToSheet sText
    '' Arguments:  sText--String with text from text file
    '' Comments:   This routine will throw an error if the
    ''             text is too long to fit within 256 columns
    '' Changes----------------------------------------------
    '' Date      Programmer   Change
    '' 6/14/06   Charley Kyd  Written
    ''======================================================
    
    ''###C0NAN### - I have Changed This To Send In the File Name as well
    ''Sub WriteToSheet(sText As String)
    ''###C0NAN### - I have Changed This To Send In the File Name as well
    Sub WriteToSheet(sText As String,sFile as String)
    
       Dim sTgtSheet As String, nTgtRow As Integer
       Dim nColCount As Integer, sChunk As String
       Dim nIncrement As Integer, rngRef As Range
    
       ''Get the controlling variables
       With ThisWorkbook
          sTgtSheet = .Names("TargetSheet").RefersToRange
    
          ''###C0NAN### - If you use this, It will overrite the rame row for each file
          ''nTgtRow = .Names("TargetRow").RefersToRange
          ''###C0NAN### - If you use this, It will overrite the rame row for each file
    
          ''###C0NAN### - Now this Will Automaticaly increment to the Next Unused Row.
          nTgtRow = Worksheets(sTgtSheet).UsedRange.Rows.Count + 1
          ''###C0NAN### - Now this Will Automaticaly increment to the Next Unused Row.
    
          ''###C0NAN### - This is here to limit the amount of characters per cell...You can Skip this if you Want
          ''nIncrement = .Names("Increment").RefersToRange
          ''###C0NAN### - This is here to limit the amount of characters per cell...You can Skip this if you Want 
    
          ''###C0NAN### - Like this - OR Later in Code
          nIncrement = len(sText)
          ''###C0NAN### - Like this - OR Later in Code
    
    
          Set rngRef = Worksheets(sTgtSheet).Cells(nTgtRow, 1)
    
       End With
    
       ''Erase any previous entries
       rngRef.EntireRow.ClearContents
    
       ''Initialize the column counter
       nColCount = 0
    
    
       ''###C0NAN### - I am going to skip this and give you another sipmler option
       ''Loop thru the string, grabbing text of specified
       ''length, writing the text to the spreadsheet
       ''Do
          ''Increment the column count
       ''   nColCount = nColCount + 1
          ''Grab the current chunk of text
       ''   sChunk = Mid$(sText, 1 + (nColCount - 1) * nIncrement, nIncrement)
    
          ''Write it to the spreadsheet
          ''###C0NAN### - Dont Worry About this row refering to Row Index= 1, as this is directly related to the Range.
       ''   rngRef.Cells(1, nColCount) = sChunk
          ''###C0NAN### - Dont Worry About this row refering to Row Index= 1, as this is directly related to the Range.
    
          ''Stop after writing a partial chunk
       ''Loop Until Len(sChunk) < nIncrement
       ''###C0NAN### - I am going to skip this and give you another sipmler option
    
       ''###C0NAN### - Here is A simpler Option
       rngRef.Cells(1, 1) = sFile
       rngRef.Cells(1, 2) = sText
       ''###C0NAN### - Here is A simpler Option
    
    End Sub
    
     
    • Thanks Thanks x 1
  4. MDSOperandi

    MDSOperandi Regular Member

    Joined:
    Jul 10, 2009
    Messages:
    224
    Likes Received:
    192
    Occupation:
    Senior Software Engineer / Internet Marketing
    Location:
    Australia
    Home Page:
    Hi, another handy home hint if you want to get the names of all the files in a directory into a text file so you can copy them into excel etc is to just drop to the command line in what ever folder you want to list all the files in and then enter

    Code:
    dir *.* > mytextfile.txt
    
    Where mytextfile.txt is the name where you want all the files stored.

    You can just get all the text documents by changing the extension like

    Code:
    dir *.txt > mytextfile.txt
    
    And you can repeat this for any extension.
    Hth
    MDS
     
  5. eddyman1us

    eddyman1us Junior Member

    Joined:
    Jan 31, 2008
    Messages:
    121
    Likes Received:
    8
    Hi , @CONAN I really appreciate your efforts , I copied your code in the Excel's Visual Basic editor and put all my files inside C:\FolderWithFiles

    When executed I got Run time Error: 53
     
  6. c0nan

    c0nan Junior Member

    Joined:
    Oct 29, 2009
    Messages:
    176
    Likes Received:
    139
    Occupation:
    Java J2EE Programmer
    Location:
    South Africa
    Ok, Lets Try this Again...

    I have Rewritten this code on a windows machine where I was able to test it.
    I have also added a "Recursive" feature.

    You can either copy the "XLS" file to the Folder containing the loose "TXT"
    files, Or you could copy the "XLS" file to a TOP level Folder. I will then Recursively scan all the folders Downwards and import ALL txt files.

    e.g:
    C:\ArticleFolder - Dump The "XLS" hers
    C:\ArticleFolder\Category 1\
    C:\ArticleFolder\Category 2\
    C:\ArticleFolder\Category 2\SubCategory 2.1
    C:\ArticleFolder\Category 3\

    The "XLS" file will scan ALL these folders, Unless you uncheck the "Recursive" option.

    In Effect if you Copy the "XLS" file to your root "C:\" it WILL scan your entire PC.

    Just Save it As a "CSV" file afterwards.

    If you keep it as a "XLS" file you can just save it and the Copy it to the Next Location to Scan.
    It Will Append the New Results to the Next Available Line in the "XLS" file



    ZIP File with Sample Data + Txt Files etc
    Code:
    http://www.rapidspread.com/file.jsp?id=asgosgzoj2
    Another
    Code:
    http://www.rapidspread.com/file.jsp?id=muxzt94inx
    VirusTotal
    Code:
    http://www.virustotal.com/analisis/6882b1e55e6fb7071ea5dbccc294b6f6fce2c38c76bc0d9af0a174ef22cb4a89-1259826781
    File TxtTest.zip received on 2009.12.03 07:53:01 (UTC)
    Current status: finished

    Result: 0/41 (0%)




    Hope This is what you were looking for.
     
    Last edited: Dec 4, 2009
  7. eddyman1us

    eddyman1us Junior Member

    Joined:
    Jan 31, 2008
    Messages:
    121
    Likes Received:
    8
    Hi , may i know what version of excel did you use ?
    I am using excel 2007 , it does show up the Dialog box , but when I click import nothing happens , maybe because it is doing that because its opening in "Compatibilty Mode" but I have enabled Macros .

    I checked the VB code , by going to the developer mode and shows the form but no code

    any idea what seems to be the problem
     
  8. c0nan

    c0nan Junior Member

    Joined:
    Oct 29, 2009
    Messages:
    176
    Likes Received:
    139
    Occupation:
    Java J2EE Programmer
    Location:
    South Africa
    Lets Try The Following.

    1. Open the Module1
    2. Go to the Function ParseFolder(MyFolderObject ......)
    3. Go to Line With: "For Each MyFileObject in MyFilesCollection.
    4. Make Sure The Curer is on that line...
    5. Press "F9" Key
    6. A Brown line should appear.
    7. Press "F5" key.
    8. One Of 4 thing could Happen
    8.1 Either The Code Will Execute And The Brown Line Will Become Yellow(Line Break Worked)
    8.2 A Form Will Appear asking which Macro to Run (Choose ParseFile, 8.1 should Now Happen)
    8.3 You will get a message telling you that you are NOT allowed to run macros(Security again)
    8.4 Nothing Happens....(Have No Answer for you here, Makes NO Sense)
     
  9. Darnals

    Darnals Registered Member

    Joined:
    Nov 2, 2009
    Messages:
    50
    Likes Received:
    19
    Occupation:
    http://cryptononline.net http://ondecity.com
    Location:
    http://gamevids.info
    Home Page:
    use freeware
    Code:
    http://csved.sjfrancke.nl/
    im usually using this program to manage the database and my Game server configuration.

    Try this okay :D
     
  10. c0nan

    c0nan Junior Member

    Joined:
    Oct 29, 2009
    Messages:
    176
    Likes Received:
    139
    Occupation:
    Java J2EE Programmer
    Location:
    South Africa
    Ok, Problem solved

    In the Code, locate this Line: (Should be in the "ParseFolder" function)

    Code:
    If (MyFileObject.Type = "TXT File") Then
    and Replace it with this line
    Code:
    If (MyFileObject.Type = "TXT File" Or MyFileObject.Type = "Text Document") Then
    Seams Different PC's Have Different Names for File Types...WTF

    The Reason the Function Does NOT display in the list of macros, is my fault.
    When I added the Parameter to the syntax.
    Macro List does NOT show Macros Requiring Parameters.

    I will Upload the ZIP again with the fixed XLS
     
    • Thanks Thanks x 1
  11. eddyman1us

    eddyman1us Junior Member

    Joined:
    Jan 31, 2008
    Messages:
    121
    Likes Received:
    8
    @Conan it works like an charm now
    Here is the Working copy of txt to csv application
    Instructions:
    1) extract the files
    2) paste the articles .txt files in the extracted folder , if you need categories you can place the text files inside the folders with category name
    3) Open TxtImport.xls and click on sheet 1 then sheet 2
    4) Dialog box will pop up hit "Import Data" and see the magic
    all the files within the root folder of the script and other folders will be imported to the sheeet save it as .csv and you are good to go .

    All thanks to C0nan !

    http://www.rapidspread.com/file.jsp?id=muxzt94inx
     
  12. c0nan

    c0nan Junior Member

    Joined:
    Oct 29, 2009
    Messages:
    176
    Likes Received:
    139
    Occupation:
    Java J2EE Programmer
    Location:
    South Africa
    Thanks for the heads up...

    I have however edited my previous Post with the new ZIP.

    I will add yours as well.
     
  13. badd

    badd Regular Member

    Joined:
    Jan 22, 2008
    Messages:
    360
    Likes Received:
    51
    Occupation:
    Innovator
    Location:
    Earth
    thks for the info, definately helps buddy