Formatting of CSV file

[LynnMac2016]

Hi

I've not been on for a while due to ill health, now I'm back and fighting fit However, my first task has me scratching my head...hoping someone can help...

Our IT team send us a .csv file with thousands of addresses but some of the Flat numbers are being converted to dates e.g.

06/04
12 Smith Street



Is being imported as

06-Apr
12 Smith Street

I've tried a couple of different things like converting the column to text and playing around with the options within the wizard with no luck.

To add to the mix, it doesn't seem to be consistent as some of the formatting has been retained correctly.

Does anyone have a solution on how to keep the original formatting?

I'd be hugely thanks :-)

Thanks

Lynn

[p45cal]

1. What steps are you using to import the csv file?
2. Attach a little version of the csv file with a few lines in, that has data in which gets converted to dates.

[LynnMac2016]

Hi, p45cal

Thanks for responding:-

1. The .csv is provided to us by our IT team
2. I have attached a sample...the yellows are being formatted as a date and the greens have retained their formatting. The green is the desired result.

We are using Excel 2016, if that helps?

Thanks

Lynn

[p45cal]

Could you answer Q1:
What steps are you using to import the csv file?
and could you supply a csv file (rather than an xlsx file)

[LynnMac2016]

Hi, p45cal

The data on the csv is an export from the organisations address checker, our IT team then provide us with the csv file. We don't actually export it ourselves.

I tried to attach a csv and got an incompatible upload format message, I've tried again using a zip file. Hope this works.

Thanks Lynn :-)

[p45cal]

So you're just double-clicking the csv file and it opens in Excel? You still haven't told me what steps you take to import the csv into Excel.
The csv file in the zip file looks like this in Notepad - so conversion has already happened before you do anything with it:
Dropbox
It's a link to a picture of your file.

[LynnMac2016]

Hi, p45cal

Sorry for being a bit dense!

So each Monday, IT send us the csv which has been generated from our online database of addresses. We don't export anything. When we double click the csv it opens in Excel. IT have provided us with a notepad version of this to help us with the formatting but it's not been that helpful.

So conversion has happened prior to them sending us the csv.

What we want to do is format the dates that should be flat numbers?

My organisation's firewall will not allow me to open your dropbox link.

Does the above information help at all?

Thanks for everything and the quick responses :-)

Lynn

[p45cal]

the csv file you sent me was like:


Add1,,Add2,Add3,add4,add5,Postcode
01-Oct,,LOVAT PLACE,HILLINGTON,GLASGOW,,G52 4DS
11-Dec,,LOVAT PLACE,HILLINGTON,GLASGOW,,G52 4XE
1/7A,,MONTROSE AVENUE,HILLINGTON,GLASGOW,,G52 4LA

I strongly suspect that the file you sent me was a file you'd already opened in Excel, deleted lots of rows and saved it under a new name. That file has been mangled by Excel. If you right click on the ORIGINAL csv file (you still have it don't you, before you opened it in Excel?) and choose Open with and choose NotePad, you should still see the likes of
06/04
or whatever in the addresses, remove a whole bunch of lower lines to avoid putting too much sensitive material into the public domain and to keep file size down, save it from Notepad under a new name (ending in .csv), then attach it here, zipped if necessary) so that I can experiment with it.

[LynnMac2016]

Hi, p45cal

You're right, due to the size and some information being sensitive, I did delete a lot of lines and a couple of columns then re-saved it.

I do still have the originals intact but I cannot share them with you due to the information contained therein.

I really appreciate all your help and patience thus far but I think I'm going to go back to our IT team and ask them to provide us with the csv duly formatted correctly.

Again many thanks.

Lynn

[p45cal]

Originally Posted by LynnMac2016

You're right, due to the size and some information being sensitive, I did delete a lot of lines and a couple of columns then re-saved it.

I do still have the originals intact but I cannot share them with you due to the information contained therein.

Just do the same as you did with Excel, but do it in Notepad.

[ArviLaanemets]

Simply rename Yourfile.csv as Yourfile.txt. Then open excel (no file opened), start open file dialog, set file type as *.txt, and open the file. You are given options like delimiter, field types, etc. Set for every column right field type, and open file - data will not be mangled then.

Maybe same options are given when you open csv file from Excel instead double-clicking it too, but I don't remember can you or not, and I don't have Excel available currently.