Solved

Save excel as Tab Delimited

Posted on 2015-02-04
42
84 Views
Last Modified: 2016-02-11
We are converting an application from MS Access to a web based system. Part of the process is to offload several tables from the Access system to a local PC, then save them as tab delimited files & upload to the web system. A custom program loads the tab delimited file into the web (MySQL) database.

One such table is the customer table.  There are a number of cases where saving the Excel as tab delimited creates strange results. See the attached for an example. Note the Excel sample has 19 rows, the tab sample 20. Note row 10 in the Excel sample gets converted into two rows in the tab delimited saved file.

Why does this happen? I'm guessing there MIGHT be tab characters in one or more of the longer text columns, for example, like columns X, Y, AB & AC.

Thanks
excel-sample.xlsx
tab-sample.txt
0
Comment
Question by:Richard Korts
  • 21
  • 18
  • 3
42 Comments
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
Hi

This is because you have TABs(ATL + Enter or CHAR(10)) in some cells. This creates that issue. You need to remove them.

These are killers in a data base.

tabs
0
 

Author Comment

by:Richard Korts
Comment Utility
Wilder1626,

How can I remove them in Excel BEFORE I save as tab delimited?

I want to just replace them with a space.
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
you can run this nice macro on your file. That will remove them all from column A to AZ.

Private Sub CommandButton1_Click()
Dim MyChar
MyChar = Chr(10)
Worksheets("Sheet1").Columns("A:AZ").Replace _
What:=MyChar, Replacement:=" ", _
SearchOrder:=xlByColumns, MatchCase:=True
End Sub

Open in new window

excel-sample-1-char-10-remove.xlsm
0
 

Author Comment

by:Richard Korts
Comment Utility
To Wilder1626,

Thanks.

Sorry that I'm so stupid, but I have no clue about creating an Excel Macro; did it YEARS ago, not in a long time.

I copied your macro text & opened the macro thing in excel; see attached. I tried to paste by putting the cursor into the dotted box; won't go there & won't paste.

It won't even let me type in there, so I see no way to create a Macro.

Thanks
macro.jpg
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
use the excel file from my previous post. the macro is on it. you will see a button on the sheet.
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
here it is again :)

Make sure you ENABLE the macros
excel-sample-1-char-10-remove.xlsm
0
 

Author Comment

by:Richard Korts
Comment Utility
I have no clue what you are talking about.

I opened the sheet you attached in excel. It has the 20 or so sample rows; the real spreadsheet has like 11,000 rows. How do I put that macro in the REAL spreadsheet?

So the Macro stuff I found is meaningless? Then why is it there?

I'm TOTALLY confused.
0
 

Author Comment

by:Richard Korts
Comment Utility
Also, I have NO CLUE how to enable Macro.
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
The easiest way is like below:
Click on ENABLE CONTENT when you open the excel file. Then click on the REMOVE CHAR(10) button

The you can create your TEXT with delimiter file

enable macro
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
This is your final file without Char(10) after the macro update.
excel-sample-1-char-10-remove.txt
0
 

Author Comment

by:Richard Korts
Comment Utility
I am not communicating correctly.

The spreadsheet I posted on EE is just a sample, an extract from a MUCH LARGER file. I picked those rows because they illustrated the issue.

I need to apply the macro to the REAL excel file.

It seems that dealing with Macros in Excel is very difficult or I am very stupid.

Is there any way I can COPY the macro from your sample sheet & put it in the REAL sheet?

Thanks
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
Look at this video on how to create a macro. That may be easier to follow.
Dont use the video macro but use mine.

The video will just show you how to create a new macro

Excel - Create Your First Macro

Once you create your first macro, you need to paste this code below on your macro. That will run the Char10 removal.

Dim MyChar
MyChar = Chr(10)
Worksheets("Sheet1").Columns("A:AZ").Replace _
What:=MyChar, Replacement:=" ", _
SearchOrder:=xlByColumns, MatchCase:=True

Open in new window

0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Why are you exporting to Excel and then to tab delimited?  You can go directly from Access to a tab delimited export and skip Excel altogether.  You will still have to remove the offending characters but you could do it in the export query or run an update query to clean up the table permanently.  Of course if you do this, you have to worry about preventing them from getting back in so perhaps doing it in the query is more expedient.

You would use the Replace() function to substitute a space for the embedded tab character.
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
Since i don't know your sheets name, you may have to update in the macro the sheet name:

Dim MyChar
MyChar = Chr(10)
Worksheets("Sheet1").Columns("A:AZ").Replace _
What:=MyChar, Replacement:=" ", _
SearchOrder:=xlByColumns, MatchCase:=True

Replace the Sheet1 by the sheet name from your file.
0
 

Author Comment

by:Richard Korts
Comment Utility
To PatHartman,

Yes, that might be better. I cannot get what Wilder1626 said to work, the video he referred uses a COMPLETELY different version of Excel than I have so I don;t have a clue.

But I'm concerned about direct export to tab delimited from Access; (1) I don't know how to do that and (2) you casually say  "Use Replace()"; I've been down that path before I CANNOT figure out how to use Replace in Access, it makes no sense.

I think I'll figure out what version of Excel I have & post another EE question about how to create a Macro in Version xx of Excel.

FYI, even figuring out what version I have is difficult; I yearn for the old days when they just had "Help"

Thanks
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
This video may be the same as your version of excel. This will be a good start if you want to learn a little more about macros.

Excel 2003
First macro
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
I like this video better :)
First macro with excel 2003
0
 

Author Comment

by:Richard Korts
Comment Utility
I have Excel 2010. The top looks like this; I hate this layout, much prefer prior.

This whole thing is getting terrible, I don't want to spend NEARLY this much time on this, I'm probably going to try to do it on the Access Export, if I can figure that out.

I'm just amazed that it's SOOOOOOOOOOOOOOOOOOOO complicated to create an Excel Macro.

Thanks
excel-top.jpg
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
Dont worry. Once you do it the first time, it will be easier for the next time.

The first step is to make the Developer tab showing on your task bar. This is where you will see all functionality for the macros.

Here is a very good video:
Show the Developer Tab
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
Let's go with the easy way.

No macros.


    1- Select the cells that you want to search in your excel file.
    2- On the keyboard, press Ctrl + H to open the Find and Replace dialog box, with the Replace tab active
    3- Click in the Find What box
    4- On the keyboard, press Ctrl + J to enter the line break character -- NOTE: Nothing will appear in the Find What box
    5- Press the Tab key on the keyboard, to move to the Replace With box
    6- Type a space character
    7- Click Find Next or Find All, to find the cells with line breaks.
    8- Click Replace or Replace All, to replace the line breaks with space characters.

here i a nice video with the full process:
https://www.youtube.com/watch?feature=player_embedded&v=s2L2CxtMwMg
0
 

Author Comment

by:Richard Korts
Comment Utility
To Wilder1626,

I think that worked; found over 300 replacements.

Let me try reloading the database table.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Richard Korts
Comment Utility
It didn't work.

I think it's carriage return (character 13). I remember doing this before.

What would be Ctrl + X for that.

Thanks
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
carriage is: Ctrl+J
0
 

Author Comment

by:Richard Korts
Comment Utility
I used Ctrl J. That didn't do it. Is it j or J (or does it matter)?

I think there is both <cr> and <lf>. I remember now doing this before (we've done this several times).
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
Try to find and replace again and holding down alt and type 013, release alt key. Then hit replace all.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Replace() is the name of a VBA function.  Some things are easy enough to find in help so I tend not to explain those.  I also found a user created replace function that was probably created prior to the version when Replace() was added to Access VBA.  I copied it and pasted it into Word because we are not supposed to post links to other forums.  I am including it because it also includes an example of how to use a function in a query.  Using the function this way will not update the underlying data.  It will replace the bad characters as the recordset is created.

Exporting a tab delimited file is done using the TransferText method if you want to automate it or via the External Data ribbon if you only need to do it once.  Because your export format is non-standard, you will have to do the export ONCE via the interface.  At the end of the process, you save the spec name and then you can refer to it in code.

docmd.TransferText acExportDelim,"specname","queryname","filename",true

The True at the end specifies column headers.  If you don't want column headers, change it to false.

To create the export spec:
1.click on the External Data ribbon.
2. Click on the query you want to export to select it
3. click on the Icon to export Text file
4. Follow the wizard.
5. On the next to the last page (back up if you go too far), press the Advanced button so you can save the spec.
6. Give the spec a short but meaningful name.  Do NOT include spaces or special characters.Next to last pageAdvanced optionsUDF-ReplaceFunction.docx
0
 

Author Comment

by:Richard Korts
Comment Utility
Says it works, doesn't. When I save tab delimited, still the same issue.

If I could just figure out the Macro.

Can you tell me how to do it in Excel 2010?
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
can you take my first macro excel file i have provided in one of my first post, and just copy paste you other excel file into that macro file. Then, you can run the macro again.
excel-sample-1-char-10-remove.xlsm
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
Sorry, take this sheet:
excel-sample-1-char-10-remove.xlsm
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
I have just recreated the macro another way in the excel file in attachment.

Copy paste your other file into this sheet. See if it works after.

 Dim x As Range
     
    For Each x In ActiveSheet.Cells.SpecialCells(xlCellTypeConstants)
        x.Value = WorksheetFunction.Clean(x.Value)
    Next

Open in new window

excel-sample-1-char-10-remove-no-2.xlsm
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
Says it works, doesn't. When I save tab delimited, still the same issue.
Did you use the function to remove the funky characters?
0
 

Author Comment

by:Richard Korts
Comment Utility
Wilder1626

"Copy paste your other file into this sheet. See if it works after."

I have NO CLUE what you mean.
0
 

Author Comment

by:Richard Korts
Comment Utility
PatHartman

I meant that comment for excel; I have not tried your idea yet.

Nightmare day, wasting the whole day on this CRAP!!!!
0
 

Author Comment

by:Richard Korts
Comment Utility
To all,

I knew I had visited this before, this question solved it , I can't remember how I did that macro, can't do it now.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28592587.html
0
 

Author Comment

by:Richard Korts
Comment Utility
To Wilder1626,

See attached. Note row 3 column X. Note how "Cell 201-0628" is on a separate line.

I did a replace ALT 013 and replace ALT 010 on this cell ALONE; nothing found.

What is the characters causing another line?

This one gets put into two rows on tab delimited. There are MANY others,

I did the replace on all the possible columns with ALT 010 & ALT 013, it found thousands. There are fewer issues than before, but the problem continues.
sample2.xlsx
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
I think there is more to validate.

I see multiple funny results when we convert in Text with delimiter. No wonder it fails.
Ex:
New results
Normally, i would expect to see:
result should be
Correct?

Where's your excel file been pulled from?

We may need to fix this from the source.

Why also convert all cells as text just by adding an apostrophe?
0
 

Author Comment

by:Richard Korts
Comment Utility
The excel is exported from Microsoft Access. I wondered about that ' too, but it seems to be OK MOST of the time.

Did you look at that other EE question? I know that solved things 100% at the time,  I just can't figure how to create a macro on Excel version 2010. Guess I figured it out then.
0
 
LVL 11

Accepted Solution

by:
Wilder1626 earned 500 total points
Comment Utility
This macro as fixed the  row 3 column X where  "Cell 201-0628" was on a separate line.

Can you give it a try?

Run the macro and create the txt file. See it it works now.
excel-sample-1-char-10-remove-no-3.xlsm
0
 

Author Comment

by:Richard Korts
Comment Utility
Is this the Macro?

Private Sub CommandButton21_Click()
 Dim x As Range
     
    For Each x In ActiveSheet.Cells.SpecialCells(xlCellTypeConstants)
        x.Value = WorksheetFunction.Clean(x.Value)
        x.Interior.ColorIndex = 17
    Next
   

End Sub
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
Yes it is.
0
 

Author Closing Comment

by:Richard Korts
Comment Utility
Thanks for sticking with me. This became an all day nightmare.

That Clean function is certainly great & does the job.

It loaded 100% into the on-line database after running the clean macro.

I also figured out Macros; probably never need them again, but...........

Thanks!
0
 
LVL 11

Expert Comment

by:Wilder1626
Comment Utility
If you try this excel file, it will remove all the chars(10) and (13), and then, create the txt with delimiter file and save it at the same place as your excel macro file.

This will save you more time

Private Sub CommandButton21_Click()
 Dim x As Range
     
    For Each x In ActiveSheet.Cells.SpecialCells(xlCellTypeConstants)
        x.Value = WorksheetFunction.Clean(x.Value)
        x.Interior.ColorIndex = 17
    Next
    
    Dim i As Long, txt As String
With ActiveSheet.UsedRange
    For i = 1 To .Rows.Count
        txt = txt & vbCrLf & Join$(Application.Transpose(Application.Transpose(.Rows(i).Value)), vbTab)
    Next
End With
 
Open Replace(ThisWorkbook.Name, ".xls", ".txt") For Output As #1
    Print #1, Mid$(txt, Len(vbCrLf) + 1)
Close #1
End Sub

Open in new window

excel-sample-1-char-10-remove-no-4.xlsm
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Create a loop to find all instances of a string 6 28
Access 2016 Merge Tables 8 25
TT Column Arrange 10 26
integer8 values 1 10
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now