How do I change Excel's delimiters when saving as .txt?

[respree]

When you save a file from Excel as a Text (.txt) file, it uses the Tab character as the delimiter. Is there a way to save it with a comma delimeter and enclose non numeric fields in quotes. I am aware of the .csv option, but it doesn't enclose the field in quotes.

Here's my problem. I'm using Excel output to feed Access input. I have more than 100,000 records, so I'm forced to cut and paste two outputs from Excel into one big text file, which will be used as import file into Access. When I combine the two text files from my text editor, it remembers the tabs and Access rejects the imported file (saying not in expected format).

Its odd, because when you export a .txt file FROM Access, the data looks like:

"field one","field two"

But when you save a file as .txt in Excel, it looks like

field one[tab]field two

Any suggestions would be appreciated.

 

[Damon Ostrander]

Hi respree,

There is a third alternative to generating the desired file, and one that is guaranteed to work: write the file using VBA I/O statement. When you do this you gain control of the delimiter, and all other characteristics as well. And the VBA Write # statement will probably do exactly what you want--I believe it uses commas for delimiters, and quotes to set off text strings.

Damon

 

[BAlGaInTl]

Found this in my searching...

Can anyone provide an example of this?

TIA

 

[dk]

Hi,

The following code writes a file using the FileSystemObject. Post back if you have any problems:-

Sub WriteFile()

    Dim oFSObj As Object, oTSStream As Object
    Dim lngRow As Long, lngCol As Long
    Dim rngeRangeToWrite As Range
    Dim strTextFile As String, strDelimiter As String
    
    
    'Choose your delimiter here.  Can be anything you want
    strDelimiter = ","


    'You can either use the 'Used range', or use something like
    'Set rngeRangeToWrite = Range("A1:E10")
    Set rngeRangeToWrite = ActiveSheet.UsedRange

    'Create a file system object and use it to create a new
    'text file.
    strTextFile = "C:\myfile.txt"
    Set oFSObj = CreateObject("Scripting.FilesystemObject")
    Set oTSStream = oFSObj.CreateTextFile(strTextFile, True)


    'This loop is for looping from the top of the sheet to the bottom
    For lngRow = 1 To rngeRangeToWrite.Rows.Count

        'This loop will write one cell at a time for the current row
        For lngCol = 1 To rngeRangeToWrite.Columns.Count

            oTSStream.Write Chr(34) & rngeRangeToWrite.Cells(lngRow, lngCol) & _
                                            Chr(34) & strDelimiter

        Next lngCol

        'The vbCr character denotes the end of a text file
        oTSStream.Write vbCr

    Next lngRow
        
    oTSStream.Close


End Sub

 

[BAlGaInTl]

Thanks, that works perfectly. I will easily be able to work it in to my current code.

Thanks again

 

[Seti]

Do you need the intermediate step of writing the txt file? Access can import directly from Excel. Do the two apps live on different machines/networks?

 

[edyfire]

Hi,

I know that this may be an old thread but i could use some help if you can.

I have used this code and changed the delimiter to a tilde, which seems to do the job great apart frm one small issue that i cant seem to solve.

It creates a txt file but the data is delimited with "~" (with quotation marks) rater than just ~ on its own.

Any ideas??

Thank you in advance