[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Difference in format between excel tab seperated file and filemaker version

Posted on 2007-07-23
22
Medium Priority
?
880 Views
Last Modified: 2008-01-09
The online ebay management system I use allows me to upload my inventory to the online database with a tab separated file.

I generate this file with filemaker, although the ebay management system was designed to accept tab separated files from excel.

The formating appears to be different in a filemaker tab file verses a excel version.  While my online database accept the filemaker exports for a small number of records, it rejects it if there are more then around 50.  If I take this file, open it in excel and resave it, then i have no problem.

While there are worse things then having to do that every time I upload to my server, I am curious as to why the 2 formating is different.

The one thing I have noticed is that when I open the filemaker generated tab file in a notepad, all of my html code has double quotes "" instead of single.  If I open the same file in excel, I can only see the single quotes.  I can't explain why.

Here is my version:
http://www.diagiodiamonds.com/ebay/Revised%20Items%20Upload%20-%20original.txt

And this is a functional version edited in excel:
http://www.diagiodiamonds.com/ebay/Revised%20Items%20Upload%20-%20fixed.txt

Note, the difference is only visable in a text editor
0
Comment
Question by:jarrodprice
  • 10
  • 8
  • 2
  • +1
21 Comments
 
LVL 11

Accepted Solution

by:
shmert earned 1000 total points
ID: 19553685
Looks like quotes are escaped correctly in the filemaker version, but not in the excel version.  FileMaker escapes the quotes by converting them to two double-quotes.  Excel leaves them alone.  As long as you don't have any tab values within a quotes string, then Excel's approach will work.

I have a feeling that your problem is caused by a value containing a tab.  Excel probably converts this to 4 or 8 spaces, while FileMaker quotes the entire string and preserves the tab character.

The ebay engine doesn't handle properly quoted tab values, so it barfs.  One fix is to scrub your data in filemaker before exporting it, by replacing tabs with spaces.
0
 
LVL 28

Expert Comment

by:lesouef
ID: 19554171
fmaker only does what you ask him to do.
a tab file means tabs in between fields whatever are data in the field.
and this may include, as shmert says, any character which may collapse the file structure; it is up to you to take care of tabs in fields, characters which must be escaped to avoid interpretation by the receiver, etc...
excel is more tricky, it may insert double quotes around a field if the field data is containing suspicious characters like tabs, which means some fields may have quotes around them, some others don't. and it will double the quotes found inside the field to avoid confusion with the quotes inserted as delimiters.
obviously your ebay app prefers the excel way. in this case, the csv format would be suited better from filemaker (quotes are mandatory) if ok with the ebay thing. otherwise, you can put quotes around all fields before exporting the file, but still have to manage characters inside fields.
I think it is a bit strange to use tab files to export html code, very risky. I think I would build up my file in a single field or variable and write it to disk. but I am lacking details to be sure this is the best way.
0
 

Author Comment

by:jarrodprice
ID: 19555772
Unfortunately, this system I use I have no control over.  It is the same management system used by all the largest ebay stores.  There is only 1 method of imported data, and I can either use it, or add data manually, which is not a solution.

That said, it is possible that there are tabs in the code.  If this is the issue, is there a custom function I could use to convert the html fields to not throw off my online database on import?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:shmert
ID: 19555958
Your best bet is removing the tabs before the data is exported.  Replace them with some spaces.
0
 

Author Comment

by:jarrodprice
ID: 19556325
I removed all the tabs and the file is still not working.

Here is the new file:

http://www.diagiodiamonds.com/ebay/Revised%20Items%20Upload%20-%20New.txt
0
 
LVL 28

Expert Comment

by:lesouef
ID: 19557334
the ebay app probably needs quotes being doubled, then the whole field in between quotes.
have you tried the excel export from filemaker?
0
 

Author Comment

by:jarrodprice
ID: 19557443
This has nothing to do with ebay itself.  This is a management system called Channel Advisor.  The question comes back to, what is the difference between the excel generated file and the filemaker version, and how can i make the filemaker version emulate the results.

I cannot create an excel export because the long text fields get messed up, among other issues.  

In order for my filemaker file to work properly with channel advisor, all i have to do is open the file in excel, and resave it.  It works, but it makes no sense that excel formats a tab separated file differently then filemaker.

0
 
LVL 28

Expert Comment

by:lesouef
ID: 19557628
it does! tab separated files is not a rigid format, all it is is that there is tab in between each field. the rest is undefined, so it is all linked to how the receiving app behaves.
but I am sure you can simulate the excel format with filemaker. it maybe an extensive script, but it can be done.
0
 

Author Comment

by:jarrodprice
ID: 19557681
I still dont understand the difference between the two files, and why i can take the file created by filemaker, and simply open it in excel and press save, and suddenly I have a different file that Channel Advisor accepts.  

Even more odd, it seems that Channel Advisor will accept the file created from Filemaker, so long as the number of records is low.  If the file has 50 or more records, I have to cut the import into smaller files, and it will accept the data.

If you have a solution, I would be thrilled  :)
0
 
LVL 28

Expert Comment

by:lesouef
ID: 19558043
the diff is that when it encounters a quote, excel doubles it then put the whole field in between double quotes.
and when you say the CA accepts the filemaker file, are you sure that the part which is imported is correct?
0
 

Author Comment

by:jarrodprice
ID: 19558081
Channel Advisor rejects the import when the number of records in the import exceeds a certain amount.  If I import the records in smaller amounts it does not reject it.  If I open the file in excel, and resave it, it imports the full file.

0
 
LVL 28

Expert Comment

by:lesouef
ID: 19558381
are field dispatched the same way in both cases?
0
 

Author Comment

by:jarrodprice
ID: 19558514
If I create a tab seperated file in filemaker, then import it into Channel Advisor, it rejects the import if the total number of records being imported exceeds a certain number.  This is not specific number, merely an observation that small imports work, large ones don't.  It is possible that I am wrong about the cause of the rejected file.

It just occured to me that chopping the file up into a smaller set of records probably has nothing to do with the reason Channel Advisor accepts it after I do so. Simply opening the file in excel and resaving it is the reason, irregardless of the size of the file after.

If i had to guess, double quotes is excel's normal handling of quotes for tab seperated files...or....it is the way it handles quotes from filemaker's tab seperated files.

Channel Advisor reads the double quotes as single quotes.  

Perhaps if I create a custom function to replace quotes with double quotes it will read properly.  
0
 
LVL 28

Expert Comment

by:lesouef
ID: 19559209
at least you can try by doing it manually once first
would the pb happen when the file is over 64k?
0
 

Author Comment

by:jarrodprice
ID: 19634354
It isn't a 64k issue.  I think I may give up on this one.  Now that I know that simply reopening and closing it in excel gets around the problem, it may not be worth the effort to reformat my exports to avoid it.

Should I just close this question?
0
 
LVL 28

Expert Comment

by:lesouef
ID: 19634967
up to you, but this is not really handy. you should compare the file before and after opening it in excel, and try to to the same in filemaker, manual solutions is always a bad choice.
about closing it, no pb, just ask for it, the topic moderator will do it
0
 

Author Comment

by:jarrodprice
ID: 19634980
The issue is how filemaker interprets quotes within text based exports.  In filemaker's help file it says the following about Comma-Separated Text format :

Quotation marks (") in a field (not curly quotes) export as double quotation marks ("").

This is the problem.  What are curly quotes?  
0
 

Author Comment

by:jarrodprice
ID: 19634988
Oddly enoughthough, the help file only says this for csv files, when mine are tab separated.
0
 
LVL 28

Assisted Solution

by:lesouef
lesouef earned 1000 total points
ID: 19635623
curly quotes are french quotes «»
another way is to use a utility to patch the exported file. I use to use the filetoolbox plug-in to do that.
Though I haven't been using it since fm6, it should still work with fm8.
or
make up the file to export by putting all records/fields to export in a global field, by using a loop to concatenate all records, and export only this field.
0
 

Author Comment

by:jarrodprice
ID: 19645458
If you get a chance lesoeuf, can you take a look at this question:

http://www.experts-exchange.com/Apple/Software/Filemaker_Pro/Q_22743050.html 


Thanks!
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20120417
Forced accept.

Computer101
EE Admin
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

834 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