• Hello Fabrik Community

    Fabrik is now in the hands of the development team that brought you Fabrik for Joomla 4. We have recently transitioned the Fabrik site over to a new server and are busy trying to clean it up. We have upgraded the site to Joomla 4 and are running the latest version of Fabrik 4. We have also upgraded the Xenforo forum software to the latest version. Many of the widgets you might have been used to on the forum are no longer operational, many abandoned by the developers. We hope to bring back some of the important ones as we have time.

    Exciting times to be sure.

    The Fabrik 4.0 Official release is now available. In addition, the Fabrik codebase is now available in a public repository. See the notices about these in the announcements section

    We wish to shout out a very big Thank You to all of you who have made donations. They have really helped. But we can always use more...wink..wink..

    Also a big Thank You to those of you who have been assisting others in the forum. This takes a very big burden off of us as we work on bugs, the website and the future of Fabrik.

Import CSV

Hugh,
I just changed the DOB to be a date element. Tried to import again, still no dates showing up in the date field - EXCEPT ONE!!! Take a look at the screenshot. No idea how that one is making it in, I am stumped. I really need to get this working for the client. What else can I try? Should I send you the most recent CSV?

Thanks for all your help, it is greatly appreciated!!!
Screen Shot 2016-01-11 at 2.30.12 PM.png
 
The value in the CSV is 10/09/17.
Looking at your other values I think this should be interpreted as MM/DD/YY, is this the way your date's form format is set?
But it is imported as YY/MM/DD, and this value (10/09/17) is the only value in your list which is valid for this format (all others would have a day >31 or a month >12).
 
Hi Troester! My CSV has a column for DOB that is formatted MM/DD/YY (see screenshot). It does not import correctly. I also have a column for SPOUSE DOB and I believe Hugh imported that for me, it is formatted the same way, but includes a time (all the times are the same 12:00AM). Does this help solve the problem?
Screen Shot 2016-01-11 at 2.48.37 PM.png
Screen Shot 2016-01-11 at 2.48.37 PM.png Screen Shot 2016-01-11 at 2.46.04 PM.png
 
What Troester is saying is that your imported data is in m/d/y format, but you have specified Y-m-d in your date element. So you've told it to expect one format, and are feeding it another. As I said in my previous response:

You'll have to set it up as a dat element, and set your 'form format' to match your import data.

Try setting the form format (under the Date Formats tab) to m/d/y.

Once you imported, the dates will be in the database in standard MySQL format, and you can set your display formats back to however you want them.

-- hugh
 
Okay, so through a bunch of trial and error, the CSV was uploaded. However, for some reason it stripped out one row and then all the data shifted up. So from record number 1 - 1400 everything was perfect. From record 1401 - end all the data was wrong. I am technical enough to manage things once they are in place, but quite obviously not technical enough to make this import work correctly. I would gladly renew my subscription, or pay someone to make this import happen for me and see it through until the kinks are worked out. Is this something you would be interested in doing, or do you have any developers you would recommend I contact for this job? Thanks!!!
 
I can take a look. If it doesn't take too long we can just call it part of Pro support, but I may need to charge if it proves to be problematic.

Make sure you My Sites info is correct, with ftp details. Put the CSV where I can ftp it, and let me know which list it has to go in.

-- hugh
 
Looks like line 1401 has a bad dob_raw of "1/23/29", with no time part like every other row has.

You have to be very specific with date/time formats on import. They must all exactly match what you have configured the element to expect.

-- hugh
 
Hugh, thanks for that. I fixed it and tried to re-import the list but it just gets hung on a white screen. As noted earlier, I am looking for someone to help do the import for me so that it gets done and is done correctly. I just keep running into problem after problem. Could you possibly import that list for me? Thanks!
 
Have you checked every date on every row to make sure it is formatted correctly? All I did was look at the line number you gave me, and right there was a badly formatted date.

The way I usually go about trying to import huge files like this is to break them up into manageable chunks of maybe 500 lines. Test importing each chunk. Empty the table between tests. If one fails, examine the data for errors - it's almost always dates, as we have no control over the error handling for date object creation.

Once each chunk imports, start again, still doing it by chunk, but don't reset the list between imports, just add the data.

I suspect one of your problems may be that the file is so huge, PHP may be running out of resources (time or memory).

I'll have a go at it here, but Monday's are a busy day for me ...

-- hugh
 
I've had another look, and created your table structure here (I used the 'element types' feature to generate an export of your table structure) so I can test.

The problem is twofold. Your dates are still messed up, you've got a mix of formatting. And the sheer size, almost 50,000 rows, is just impractical to try and import though Fabrik, or any other web based importer.

I'm attempting to get it done using some other tools, to import it directly into MySQL. The approach is to use Excel (well, Libre Office) to import it again and convert the dates to a standardized format. Unfortunately that can't convert it to MySQL's format. So my approach is to change the datetime fields to varchar, so I can at least import the format we have. Then create some temporary columns I can then attempt to convert that stand format into something MySQL can understand.

However, this could take many hours, and it may not be perfect - there may be some missing dates.

-- hugh
 
OK, I have it imported here. I went about it in this way:

Rather than try and import through Fabrik, which is just impractical with 50,000 records and a date format which won't convert, I imported it in place in MySQL.

First I removed the 'id_raw' column from the CSV, and exported it with "quote all text fields".

Then I changed the three date fields in my version of your table to be just simple VARCHAR, rather than DATETIME fields.

Then I imported the data with this command:

Code:
TRUNCATE TABLE ia_clients;
LOAD DATA INFILE 'c:/Temp/ia_clients_big.csv'INTO TABLE ia_clients
FIELDS TERMINATED BY ','ENCLOSED BY '"'LINES TERMINATED BY '\n'
IGNORE 1 LINES
(first_name, last_name, address, city, state, zip, county, home_phone, mobile_phone, alt_phone, email, dob, email_2, spouse_name, spouse_dob, year_retired, notes, workshop, workshop_year, ARTA, Prospect_Client, birthday_month_year, product_health, product_life, product_annuity, product_ltc, product_med_supp, product_pdp, product_dental, product_investments, product_auto, product_advantage)

Then I created three new columns, dob_new, spouse_dob_new and date_retired_new, all as DATETIME.

Then I ran these two command for each of the dates:

Code:
update ia_clients set dob_new = STR_TO_DATE(dob,'%m/%d/%y %h:%s %p');
update ia_clients set dob_new = date_sub(dob_new, INTERVAL 100 YEAR);

The second command is necessary because with 2 digit years, MySQL obviously doesn't know which century to use, so for years less than 69 is sets them to the 2000's.

Then I renamed the original three date fields to dob_old, etc, and renamed the three new ones back to the original names.

This seems to have worked, although the year_retired in your original data seems to be basically non-existent.

So the only issue I can see is that if you had any dates in the there which had a year of >69, those will now be in the 1800's. But that's fixable.

So I now have an SQL table which we can import into your system.

Would you rather I simply upload the SQL file and you run the import in phpMyAdmin, or would you like me to do it? If you want me to do it, fill out your My Sites with the information I need to log in to phpMyAdmin.

-- hugh
 
Hugh, thanks again so much for all your work, but it is still not right. Spot checking the DOB all the records AFTER Robert Fletcher (record 1401) have the DOB of 2.16.16
I really need to get a working solution for my client, or lose their business. Any chance there is a fix that could happen today, or is their some other Joomla extension that you would recommend that would be better suited to this project? I love Fabrik, and it would work PERFECTLY for me, if we could just get this first import in the system correctly.

Thanks again for all your help!!!
 
We are in need of some funding.
More details.

Thank you.

Members online

Back
Top