Upload CSV File with Date into mySQL Table

Would WPMU be able to assist uploading a CSV file into an existing WordPress table?
I need to add a bunch of records to an existing table and I think I have all of the rows correct, but I keep getting an error on the date column. There are only six columns including the ID for each row.
The CSV file to upload is about 1260 rows.
Of course, I've got a good backup of the database and the full site.
Obviously, I can provide credentials to access and the CSV file via private means.

  • Milan
    • WordPress Wizard

    Hello Steve McGough

    Hope you are well today and thanks for asking us. :slight_smile:

    but I keep getting an error on the date column

    What is error actually ? Would you tell me in depth ? Or just copy paste error from there to here. :slight_smile:

    Looking forward to hearing back.
    Kind Regards,
    Milan

  • Steve McGough
    • Design Lord, Child of Thor

    The error:
    MySQL said:
    #1292 - Incorrect datetime value: '' for column 'date' at row 1

    When I look at the table in PHPmyAdmin, there are six fields including the ID. The final field is date, and it is formatted as 2016-07-05 01:07:14

    When I export the table as a CSV, the format changes to 7/5/2016 1:07:14 AM, but in the CELL, it is shows as 7/5/2016 1:07 without the seconds and the AM/PM indicator.

    The spreadsheet I am looking to import has the date formatted as 2015-07-15 01:12:45  and it is shown the same way in the cell and the cell detail view.

    Screenshot of the admin view - https://grayguns.com/wp-content/uploads/2016/07/admin-view.png

    Screenshot of what the table exported looks like - https://grayguns.com/wp-content/uploads/2016/07/csv-export.png

    Screenshot of what I'd like to import - https://grayguns.com/wp-content/uploads/2016/07/to-import-2.png

    I'm half-way to understanding this, but can not crack it. Thank you for any support you can provide.

  • Adam Czajczyk
    • Support Gorilla

    Hello Steve!

    Thank you for all this additional information and screenshots. I'd like to ask you some more questions though.

    Are you trying to import that data to the table that's "fully custom" or that comes from some plugin (if so, what's the plugin)?

    Could you share these export files with me so I could test it on my end:
    - structure of the table that data is about to be imported to ("structure export" file from phpMyAdmin)
    - full or partial import file

    Simplest way to share these files would be to upload them to some file sharing/file cloud service and share a link to it here (of course please strip all the sensitive data).

    Best regards,
    Adam

  • Steve McGough
    • Design Lord, Child of Thor

    The plugin is WooWaitlist, https://codecanyon.net/item/woowaitlist-woocommerce-back-in-stock-notifier/7103373 - The plugin uses one table and it's a pretty straight-forward setup.

    I actually have a support request in with the author of the plugin as well, but have not heard back in some time.

    Here is a link to the structure of the table and the import file, excluding the email addresses:
    https://grayguns.com/wp-content/uploads/2016/07/files.zip

    Thank you!

  • Milan
    • WordPress Wizard

    Hello Steve McGough

    Hope you are well today and thanks for zipping files :slight_smile:

    After exporting one of sql files from your zip, to one of my local database, I was able to create one table with good schema structure. :slight_smile:

    Surprising thing in that table I found that, date field of table is only ready to accept TimeStamp. You can see that in my this captured snapshot here,

    So now we can try another approach to import data in CSV with timestamp. :slight_smile: Actually every major excel software provides way to date cell values in timestamp. .You can check this article for Microsoft Office Excel,
    https://www.extendoffice.com/documents/excel/2473-excel-timestamp-to-date.html

    My advice to you is to take backup of your CSV file first. Then convert date cells in timestamp and finally try to import those data.

    Let me know how it goes. :slight_smile:
    Kind Regards,
    Milan

  • Steve McGough
    • Design Lord, Child of Thor

    I was having an issue converting '2016-05-14 10:08:09' (as an example) to a TIMESTAMP so I just changed all of the dates to 7/1/2016, then converted to TIMESTAMP which is '1467331200'. I really don't care what the date is for the previous records as it does not matter to the plugin.

    Took the test CSV file and tried to upload.
    https://grayguns.com/wp-content/uploads/2016/07/test-import.zip

    At first, I tried with NO id column (the first column). Received the following message: "Invalid column count in CSV input on line 1."

    Since I did not have an id column, I added one and left the data blank. (Cell A1 had id and all cells below were empty.)
    Got the following error:

    SQL query:
    INSERT INTO  <code>wp_woocommerce_waitlist</code>
    VALUES (
    'id',  'email',  'productid',  'variationid',  'grouped_parentid',  'date'
    )
    MySQL said: Documentation
    #1366 - Incorrect integer value: 'id' for column 'id' at row 1

    I got the same error when I tried the CSV file with numbers in the first column. (Assigned IDs to each row that did not duplicate what is already in the row.)

  • Milan
    • WordPress Wizard

    Hello Steve McGough

    Hope you are doing well today. :slight_smile:

    After some more findings, finally I was able to import data to WordPress table. I've recorded small video for you to demonstrate how I did that. :slight_smile:

    Hope you find it useful. :slight_smile:

    Video: http://take.ms/lWmVH
    ( Sorry for background audio, I forgot to mute it. )

    If you have anymore questions, please don't hesitate to ask me. :slight_smile: I'd love you help you. :slight_smile:
    Kind Regards,
    Milan

  • Steve McGough
    • Design Lord, Child of Thor

    I'm finally getting back to this and appreciate your help.
    There is still an issue with the date column. The export file I sent you somehow got changed as the date column in your video shows the number in scientific notation or something similar. That's not what I'm working with and I'm still getting the same error:
    #1292 - Incorrect datetime value: '' for column 'date' at row 1

  • Adam Czajczyk
    • Support Gorilla

    Hello Steve!

    I downloaded and checked the files again and gave it another spin. Let's first make sure that the file "to be imported" (.xlsx file) is correct. Below is a screenshot of what I see after opening it in a spreadsheet:

    The steps I'm about to describe below require Libre Office to be used. I mean: they can almost surely be re-created using Open Office and most likely other software of that type but Libre Office worked great here. It's free and you can get it from here:

    http://www.libreoffice.org/download/libreoffice-still/

    I also assumed that the database table already exists.

    1. Open the xlsx file in Libre Office: don't use any import etc features, just open it.
    2. Go to "File - Save As" menu
    3. From the "Save as type" drop-down box select "CSV Text (.csv) (*.csv)
    4. Make sure that the "Edit filter settings" option is checked and click "Save"
    5. A filter window will show up so from "Character set" drop-down list select "Unicode (UTF-8)"; don't change other settings here;
    6. Hit "OK"
    7. Hit "OK" if a warning about saving only an active sheet comes up
    8. Also select to save as CSV in case it asked you whether you wish to save CSV or stick to original file format :slight_smile:

    At this point you should end up with a properly formatted CSV file. Next steps are performed with phpMyAdmin

    9. Select your "wp_woocommerce_waitlist" table in phpMyAdmin
    10. Switch to "Import" tab
    11. Use "Browse" button to select CSV file you just created
    12. Make sure that Character Encoding (below the browser button) is set to "utf-8"
    13. Make sure that the format in "Format" drop-down box is set to CSV
    14. Put following into the "Column names:" field:

    email,productId,variationId,grouped_parentId,date

    15. Hit "Execute"

    On my end this produces the properly imported data as shown on the screenshot below (unless I messed my understanding of date formatting requested):

    Could you please give it a try and let me know if this worked for you?

    Best regards,
    Adam

  • Steve McGough
    • Design Lord, Child of Thor

    Yeah, still the same. I downloaded the Mac version of the software you suggested and followed your steps. Very strange...

    Error
    SQL query:
    
    INSERT INTO  <code>wp_woocommerce_waitlist</code> (  <code>email</code> ,  <code>productId</code> ,  <code>variationId</code> ,  <code>grouped_parentId</code> ,  <code>date</code> )
    VALUES (
    
    'Acesn88@q.com',  '1864',  '1868',  '0',  '2015-10-09 03:50:28  '
    )
    MySQL said: Documentation
    
    #1292 - Incorrect datetime value: '2015-10-09 03:50:28 Â ' for column 'date' at row 1
  • Adam Czajczyk
    • Support Gorilla

    Hello Steve!

    This part of the last error line

    03:50:28 Â

    suggest some charset encoding issues. Have you followed my solution exactly step by step, paying special attention to "UTF-8" conversion steps? I'm sure you did but it's always better to double-check everything.

    Is the site you're working with a live site or a staging/dev site? I'm asking this because I think I'd like to give it a try myself if you don't mind providing me with a full access credentials. I wouldn't want however to "break" a live site in case anything went wrong.

    That being said, do you think it'd be possible if I tested it your site? If yes, please let me know here and I'll let you know how to securely provide me with access credentials.

    Best regards,
    Adam

  • Steve McGough
    • Design Lord, Child of Thor

    Followed the steps and I just double checked and tried again.

    Attached are screen shots of the settings as I went through saving the file.

    I have my server host checking to see if there is something different going on with my installation as compared to your set up.

    I only have a production environment, but can build out a dev if needed.

    Hang on... and thank you!

    Steve

  • Adam Czajczyk
    • Support Gorilla

    Hello Steve!

    It would be great then if you could setup a dev environment (that'd be an exact copy of production one!) that I could access and check for myself.

    Once you're ready with it please send in:

    Subject: "Attn: Adam Czajczyk"

    - Mark to my attention, the subject line should contain only: ATTN: Adam Czajczyk
    - Do not include anything else in the subject line, doing so may delay our response due to how email filtering works.
    - Link back to this thread
    - login URL and admin account login credentials (may be a temporary admin account) data
    - Include FTP log-in details (hostname, username & password)
    - Include hosting control panel access details (login address, username & password) or phpMyAdmin access details
    -- cPanel's usually the control panel used for this, but your provider may use something else; I'll need this for accessing your site's database, preferably via phpMyAdmin
    - Include any relevant URLs for your site

    Please use our contact form here http://premium.wpmudev.org/contact/.
    Select "I have a different question" from the drop-down list.

    Please be sure to make a full backup of your entire site first!

    Also let me know here that you're ready and that you sent me a message.

    Best regards,
    Adam

  • Steve McGough
    • Design Lord, Child of Thor

    Will do. I've built out a development area with an exact duplicate of the site. My host - who already has access - has offered to import the file just now.

    If they succeed, we're all good. If they have issues, I'll get you the login info straight away.

    Steve

  • Steve McGough
    • Design Lord, Child of Thor

    We finally got it. There were two spaces after 2015-10-09 03:50:28 in the date column for some reason. I had to jump through multiple hoops to remove those two spaces without Excel converting the information into 9/10/15 3:50. Even in Libre Office, I was not able to just remove the two extra spaces. Had to convert it to a plain text file, remove the spaces and bring back in.
    Thank you for your help!

Thank NAME, for their help.

Let NAME know exactly why they deserved these points.

Gift a custom amount of points.