CSV date format

Question

I would like to have the rows that are not being displayed but get an error subquery returns more than 1 row

This works fine;

SELECT CONCAT( P.LastName, ', ', P.FirstName ) AS Name, 
(
SELECT MP.MembershipID
FROM membershipperson MP
WHERE MP.PersonID = P.PersonID
) 
AS MembershipID, PersonID
FROM `person` P

This fails when I add the ORDER BY 'MembershipID' it works if I ORDER BY 'PersonID';

SELECT CONCAT( P.LastName, ', ', P.FirstName ) AS Name, 
(
SELECT MP.MembershipID
FROM membershipperson MP
WHERE MP.PersonID = P.PersonID
) 
AS MembershipID, PersonID
FROM `person` P
ORDER BY `MembershipID` ASC 

How do I get the list with all the MembershipID rows?

Solution 1

You can use the Format VBA function:

Format(DateText, "dd/mm/yyyy")

That will format it how ever you like.

For a more permanant solution, try changing your regional settings in windows itself, Excel uses this for its date formatting.

Start -> Settings -> Control Panel -> Regional Options.

Make sure that the language is set to whatever is appropriate and that the date settings are as you want them to be

Solution 2

When I run into this problem I usually write out the dates as yyyy-mm-dd which Excel will interpret unambiguously.

Solution 3

First of all the other answers are all good but theres some more information you might find helpful

A CSV file only contains text. That is the data is not in date format but in a text format. So when you open a CSV file in Excel, Excel by default interprets that data for you. It doesn't have to. You could force it to leave it as text, Or as mentioned by Mark you can add code into your import macro that alters it for you. If you want an automated process then this is the best solution. Simply add the code in VBA to the macro that applies the required date format to the column with the date data. Alternatively you could do this manually after the file is open and the data has been pasted by selecting the column yourself and chancing the format. You can customise number formats (choose custom) and then write it up yourself. Eg dd/mm/yyyy.

Comments

[Shoban]

The number of records is high and there are many sheets in import.xls. Any other method to do it automatically?

[Mark]

i have just edited this post, take a look at changing the windows settings themselves

[Shoban]

Sorry I missed to mention this. The regional settings are correct and as expected. Thanks

[Simon]

this is the most robust of the solutions recommended here. The way that Excel and VBA interact with the system locale is quite opaque and it's easy to get weird results. Using this method avoids a lot of code and is usually not to problematic for the users.

[Charlie Schliesser]

"So when you open a CSV file in Excel, Excel by default interprets that data for you. It doesn't have to. You could force it to leave it as text" How do you force it to leave it as text?

[Bob Cooper]

I don't see the INNER JOIN there. Whith this I get; Column 'PersonID' in field list is ambiguous

[jarz]

The inner join is implicit. If you want it explicitly:

[jarz]

SELECT CONCAT( P.LastName, ', ', P.FirstName ) AS Name, MP.MembershipID AS MembershipID, PersonID FROM person P INNER JOIN membershipperson MP ON MP.PersonID = P.PersonID

[Bob Cooper]

Whith this I also get the PersonID in field list is ambigous

[Bob Cooper]

I get a major failure with this. If you like I can share it with you but the message is very long.

[jarz]

What DBMS are you using?

[AgRizzo]

@user3066675 - Are you sure you copied the query correctly? Every column is being qualified in this query with the table alias.

[Bob Cooper]

Sorry I made a mistake with your code. But still get this message... Column 'PersonID' in field list is ambiguous. I am new to this so please be patient with me. THANKS!

[Bob Cooper]

I think your asking how I manage the db. I am in the database portal using phpMyAdmin on the host server.

[jarz]

Yeah, I see why you get that, just add P. to the PersonID on the field list. Like this: SELECT CONCAT( P.LastName, ', ', P.FirstName ) AS Name, MP.MembershipID AS MembershipID, P.PersonID ....

[Bob Cooper]

Your right I missed something it appears to give me what I'm I looking for now... THANK YOU

[Bob Cooper]

José, This also worked, more than one way to skin a cat. I get the same list as the code that Stephan offered. Thanks to you both. Bob

[Stephan B]

I edited the SQL a few seconds after posting it, SO should have shown it anyway. Probably like José too, I first copied PersonID without qualifying it.

[BengalTigger]

In your CSV file, wrap it with quotes and precede with an equal sign as follows: ="dd/mm/yyyy"