Formatting Problem

[randy700]

Not sure if this is the right forum for this question, so please point me in the right direction.

We are currently sending a main frame file, through the IBM MQ system, to create a .csv file. This csv file is then sent to the customer's Lotus Notes email account, which opens it as an Excel spreadsheet.

The problem occurs in my Part Number column. The part numbers are text fields, but some are only numbers. To make matters worse, some have 0 (zero) as the first number in the part number.

Excel treats these cells as numeric. Right justified and no leading zeroes. At present, the customer has to manually format the column as text to get it left justified. (Not sure how he's handling the leading zero situation.)

Does anyone know what needs to be done to get Excel to treate these fields as text? I've tried adding a single quote to the beginning of the field when it's created on the main frame, but the single quote then appears in every part number field. It causes the field to be left justified and retains the leading zero, but we don't want to look at the single quote!

If I've left out any necessary details, please ask and I'll try to provide the answers.


Randy

 

[dallen43]

I'm far from on expert, but when I've come across this I've done one of two things:

1. Format the output with pipe (|) delimiters and use a different extension. Then have the user open the file with Excel. (It won't happen automatically.) When the text import wizard comes up, they can select delimited by and enter the pipe character. In step 3, they can se the field to text rather than general.

2. Do the above but create a template spreadsheet which you provide to the users. They open the template and hit a button or menu option and the system imports the file with the correct options.

The problem appears to be that Excel opens the .csv file with the assumption that all fields are general. General assumes any cell with only numeric characters to be numeric.

If any of the more experienced users have a differnt way around this, I would be very happy to know it.

Deb

 

[Gavona]

I've tried adding a single quote to the beginning of the field when it's created on the main frame, but the single quote then appears in every part number field. It causes the field to be left justified and retains the leading zero, but we don't want to look at the single quote!

Try highlighting the column in Excel then Edit,Replace to replace the quotes with nothing.

Gavin

 

[randy700]

My question regards building this programatically. I'm not the end user. I'm attempting to create this so he can simply open the Lotus Notes attachment and work with the data. I don't want him to be forced to do any formatting if I can find a way around it.

I know how to format the cell/column once it's been opened but that's not what I'm trying to accomplish here.


Randy

 

[SkipVought]

Rather than OPENING the file as a workbook, open a new workbook and then IMPORT the .csv file using Data > Import External Data > IMPORT. Then you can define that column as TEXT rather than GENERAL, and all will be well.

It get new data, assuming the the mainframe .csv file has the same filename, all you have to do is Data > Refresh. If the file name changes, Data > Refresh can still work if you point to the new .csv file.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[randy700]

All,

I have no difficulties in formatting the workbook AFTER it's been opened. The question I've posed here refers to formatting the fields BEFORE the workbook is opened.

I'm simply trying to present my customer with a more professional looking output so he will not have to do any formatting himself.

It appears there may be no way to accomplish this.

Thanks for the attempts.

Randy

 

[lionelhill]

If your customer can cope, try getting your system to export the file with the file-extension txt instead of csv. Excel will still open it (albeit with questions about column delimiters and fixed widths), but will (probably) treat everything as straightforward text.

I've had to resort to this where columns contain entries such as 21a3, 32c15 etc., because when I reach 05e07 you can imagine what happens....

 

[Gavona]

Give the customer a workbook template with an event driven bit of code that opens the latest file in a standard location and applies one of the solutions already suggested (Skip's would be my choice).

Gavin

 

[randy700]

so he will not have to do any formatting himself

Excel will still open it (albeit with questions about column delimiters and fixed widths)

If he has to answer these questions, it does NOT solve the problem.

IMPORT the .csv file using Data > Import External Data > IMPORT

If he has to do ANYTHING other than open the file, it does NOT solve the problem.

Thank you all for your input. I'm afraid this is one situation that will have to remain unresolved. I have the file opening fine. My customer will simply have to format the one column himself.

Randy

 

[DaveInIowa]

Could your customer work with the data if you formatted it in the .csv as a formula?

PartNumber, Column2, Column3
="001",c2val1,c3val1
="002",c2val2,c2val3

 

[SkipVought]

"If he has to do ANYTHING other than open the file, it does NOT solve the problem."

YOU, YOU, YOU...

you set up the QueryTable for the import, with the PROPER PARSING.

Then you can set the appropriate option in Data Range Properties, so that the QueryTable refreshes when the workbook opens.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

The problem is with using a csV file

the V stands for VALUES seperated by commas. Because Excel knows this it imports based on these specs. If you use a .TXT file you get the file import wizard becasue .TXT is not prescriptive about the delimeter or the data type held in the file whilst a csv is

Your real issue is actually a database issue which is: Why are you storing a numeric field as text and one with leading zeroes at that?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[randy700]

Why are you storing a numeric field as text and one with leading zeroes at that?

Because it's a part number.


Randy

 

[xlbo]

why does that mean it needs to be stored as text?

Part number really should give the game away - I would expect a part NUMBER to be numeric. A part ID could be alpha/numeric

I see however that you have some part numbers that are alpha numeric so fair enough but in that case, you really should have ALL part numbers as alpha numeric - otherwise you get mixed data types which causes issues like the one you have now...

In short, without intervention you are pretty stuck because you have effectively mixed data types in your database.

I would favour Skip's approach (albeit using text import rather than MS Query) whereby all that needs to happen is set up an "Import Workbook" which will open a file in a certain directory and run code which imports the file as if it were a .txt file with the appropriate parameters set. All the customer would need to do is save teh csv file to a specific location, open the import workbook and press a button....that would be the path of least resistance for the customer. Alternatively, you could make your data clean and make all part IDs proper alpha numeric

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[randy700]

xlbo,

I sincerely doubt I can convince a billion dollar corporation, with hundreds of thousands of parts, to change their part number nomenclature simply because one person seems to have a spreadsheet problem.

Everyone here has offered solutions that involve some action on the customer's part beyond that of opening the file when it's received in email. Unfortunately, the only action he wants to take is to open it when it arrives in his inbox. And, he has a high enough position to expect such niceties.

Fortunately, he's also fair enough to accept the response that it simply isn't doable -- at least, not with any procedures I've found to date.


Randy

 

[dallen43]

Would it be possible to automate an intermediate step where the output file (currently .csv) is imported into Excel using the correct formatting. Then send the .xls file to the user?

 

[Gavona]

Could you put code into his Personal.xls to detect when the csv file is opened and then run code to resolve your issues?
Something like:

http://www.ozgrid.com/forum/showthread.php?t=43540

Gavin

 

[xlbo]

That's fair enough Randy - I was simply pointing out that this isn't really a spreadsheet issue - Excel is doing exactly what it should do. Billion dollar corporation or not - it is their database design that is causing the issue.

Given that you are creating the csv file in house and then sending it, how is the file being sent? If it is a manual process then you have an opportunity to intercept it before it is sent, do some processing and send as an xls file as per dallen43... As far as I can see if changing the database isn't an option and asking the user to do something isn;t an option then the only option left open to you is to amend the process of the generation of the file to send...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question