MySQL Forums
Forum List  »  MySQL Workbench

Re: CSV import for files with ' apostrophe in the text
Posted by: David Fretwell
Date: August 11, 2020 04:02AM

Robert,

Sorry I missed your response. The table is created as part of the import process. I have tried just leaving the fields types unchanged along with setting them all to text. I have pasted the table structure and few rows from the test file below.

So the create table structure looks like that is built by the import wizard.

CREATE TABLE "line listing by ae (sitecode)test2" (
"Date of extract" text,
"Project Code" text,
"Site code" text,
"Site/Trust name" text,
"AE Identifier" text,
"PV Unique Study Identifier ID (Patient ID)" text,
"Date Recorded" text,
"Age of Onset Adult" text,
"Extent of UC Adult" text,
"Age of Onset Paed" text,
"Extent of UC Paed" text,
"Behaviour Crohn's Adult" text,
"Location Crohn's Adult" text,
"Behavior Crohn's paed" text,
"Location Crohn's Paed" text,
"Disease Proximal to terminal ileum (Montreal L4)" text,
"Perianal Crohn's Disease" text,
"Disease Proximal to terminal ileum (Montreal L4a L4b)" text,
"Year of Birth" int DEFAULT NULL,
"Disease Classification" text,
"Height" double DEFAULT NULL,
"Weight" int DEFAULT NULL,
"Medical History" text,
"Medications for other conditions" text,
"Concomitant medications fo IBD" text,
"Date AE reported" text,
"Description of AE" text,
"AE Start date" text,
"AE is Start date approx." text,
"AE Date resolved" text,
"Is date resolved approx." text,
"PV Drug" text,
"Drug Units" text,
"Drug Dose" int DEFAULT NULL,
"Drug Frequency" text,
"Drug Route" text,
"Batch number" text,
"Date drug Started" text,
"Date drug stopped" text,
"Reason drug stopped" text,
"Severity" text,
"Is the AE related to the Drug?" text,
"SAE Category" text,
"AESI Category" text,
"Reported to authorities (yellow card)" text,
"Pregnancy Y/N" text,
"Partner / Patient pregnant" text,
"EDD" text,
"Date of delivery" text,
"Outcome of pregnancy" text,
"Outcome of pregnancy (other)" text,
"Action taken as a consequence" text,
"Outcome of AE" text,
"Date of next review" text
);

This is a copy of some test data that I am trying to load. CSV File between --

--

Date of extract,Project Code,Site code,Site/Trust name,AE Identifier,PV Unique Study Identifier ID (Patient ID),Date Recorded,Age of Onset Adult,Extent of UC Adult,Age of Onset Paed,Extent of UC Paed,Behaviour Crohn's Adult,Location Crohn's Adult,Behavior Crohn's paed,Location Crohn's Paed,Disease Proximal to terminal ileum (Montreal L4),Perianal Crohn's Disease,Disease Proximal to terminal ileum (Montreal L4a L4b),Year of Birth,Disease Classification,Height,Weight,Medical History,Medications for other conditions,Concomitant medications fo IBD,Date AE reported,Description of AE,AE Start date,AE is Start date approx.,AE Date resolved,Is date resolved approx.,PV Drug,Drug Units,Drug Dose,Drug Frequency,Drug Route,Batch number,Date drug Started,Date drug stopped,Reason drug stopped,Severity,Is the AE related to the Drug?,SAE Category,AESI Category,Reported to authorities (yellow card),Pregnancy Y/N,Partner / Patient pregnant,EDD,Date of delivery,Outcome of pregnancy,Outcome of pregnancy (other),Action taken as a consequence,Outcome of AE,Date of next review
19/06/2020 16:04,Test project 2,T1,Test Site,T1_Test Site00783,1508,,>40,,,,Non-stricturing non-penetrating,Terminal Ileum+/- limited caecal disease,,,Yes,Yes,,1934,Crohn's Disease,1.56,67,Yes,CHD~~*Breast cancer,Methotrexate,08/06/2019,Rash,08/06/2019,No,,,,g,1,3w,SC,xyz,01/06/2019,,,Moderate,Yes,No,No,No,No,,,,,,Dose not changed,Resolving,
19/06/2020 16:04,Test project 2,T2,Test Site,T2_Test Site00785,1508,,>40,,,,Non-stricturing non-penetrating,Terminal Ileum+/- limited caecal disease,,,Yes,Yes,,1934,Crohn's Disease,1.56,67,Yes,CHD; Breast cancer,Methotrexate,15/06/2019,Headache,14/06/2019,Yes,,,,g,1,3w,SC,xyz,01/06/2019,,,Moderate,Yes,No,No,No,No,,,,,,Dose not changed,Ongoing,
19/06/2020 16:04,Test project 2,T3,Test Site,T3_Test Site00788,1508,,>40,,,,Non-stricturing non-penetrating,Terminal Ileum+/- limited caecal disease,,,Yes,Yes,,1934,Crohn's Disease,1.56,67,Yes,CHD; Breast cancer,Methotrexate,22/06/2019,Itching,20/06/2019,Yes,,,,g,1,3w,SC,xyz,01/06/2019,,,Severe,Yes,No,No,No,No,,,,,,Dose not changed,Resolving,
19/06/2020 16:04,Test project 2,T4,Test Site,T4_Test Site00790,1508,,>40,,,,Non-stricturing non-penetrating,Terminal Ileum+/- limited caecal disease,,,Yes,Yes,,1934,Crohn's Disease,1.56,67,Yes,CHD,Methotrexate,29/06/2019,Abdominal pain,22/06/2019,Yes,,,,g,1,3w,SC,xyz,01/06/2019,,,Moderate,Yes,No,No,Not Known,No,,,,,,Dose not changed,Ongoing,
19/06/2020 16:04,Test project 2,T5,Test Site,T5_Test Site00792,1513,,17 - 40,Left sided UC (distal UC),,,,,,,,,,1990,Ulcerative Colitis,1.7,89,No,,,25/04/2020,Pain on injection worsened. Drug stopped at patient's request,25/04/2020,No,,,,g,2,4w,SC,abc,18/04/2020,20/05/2020,Side effects/adverse reaction,Severe,Yes,Medical importance,No,Yes,No,,,,,,Drug withdrawn,Ongoing,
19/06/2020 16:04,Test project 2,T6,Test Site,T6_Test Site00796,1518,,17 - 40,Extensive UC (pancolitis),,,,,,,,,,1990,Ulcerative Colitis,1.4,56,No,,,29/01/2020,Pain at injection site has become intense,01/01/2020,No,,,,g,5,2w,SC,def,01/01/2020,,,Moderate,Yes,No,No,Yes,No,,,,,,Dose not changed,Ongoing,
19/06/2020 16:04,Test project 2,T7,Test Site,T7_Test Site00799,1518,,17 - 40,Extensive UC (pancolitis),,,,,,,,,,1990,Ulcerative Colitis,1.45,67,No,,,05/02/2020,Thirsty all the time,10/01/2020,Yes,,,,g,5,2w,SC,def,01/01/2020,,,Moderate,Yes,No,No,No,No,,,,,,Dose not changed,Ongoing,
19/06/2020 16:04,Test project 2,T8,Test Site,T8_Test Site00801,1518,,17 - 40,Extensive UC (pancolitis),,,,,,,,,,1990,Ulcerative Colitis,1.45,69,No,,,01/05/2020,Pregnant,30/04/2020,Yes,,,,g,5,2w,SC,def,01/01/2020,,,Mild,No,No,No,No,Yes,Patient,25/12/2020,,,,Dose not changed,Ongoing,
19/06/2020 16:04,Test project 2,T9,Test Site,T9_Test Site00803,1523,,17 - 40,,,,Stricturing,Colonic,,,No,No,,1950,Crohn's Disease,1.78,89,No,,,08/06/2019,Rash,05/06/2019,Yes,,,,g,1,2w,SC,,03/06/2019,,,Moderate,Yes,No,No,Not Known,No,,,,,,Dose not changed,Ongoing,
19/06/2020 16:04,Test project 2,T10,Test Site,T10_Test Site00805,1523,,17 - 40,,,,Stricturing,Colonic,,,No,No,,1950,Crohn's Disease,1.78,89,No,,,15/06/2019,Headache,10/06/2019,Yes,,,,g,1,2w,SC,,03/06/2019,,,Severe,Yes,No,No,No,No,,,,,,Dose not changed,Ongoing,
19/06/2020 16:04,Test project 2,T11,Test Site,T11_Test Site00807,1523,,17 - 40,,,,Stricturing,Colonic,,,No,No,,1950,Crohn's Disease,1.78,89,No,,,22/06/2019,Itching,22/06/2019,No,,,,g,1,2w,SC,,03/06/2019,,,Moderate,Yes,No,No,Not Known,No,,,,,,Dose not changed,Ongoing,
19/06/2020 16:04,Test project 2,T12,Test Site,T12_Test Site00809,1523,,17 - 40,,,,Stricturing,Colonic,,,No,No,,1950,Crohn's Disease,1.78,85,No,,,29/06/2019,Abdominal pain,22/06/2019,Yes,,,,g,1,2w,SC,,03/06/2019,,,Moderate,Yes,No,No,No,No,,,,,,Dose not changed,Ongoing,
19/06/2020 16:04,Test project 2,T13,Test Site,T13_Test Site00811,1528,,17 - 40,Extensive UC (pancolitis),,,,,,,,,,1967,Ulcerative Colitis,1.8,90,Yes,Migraine,Azathioprine/Mercaptopurine,25/04/2020,Pain on injection now intolerable,25/04/2020,No,,,,g,2,3w,SC,ghi,18/04/2020,20/05/2020,Side effects/adverse reaction,Moderate,Yes,Medical importance,No,No,No,,,,,,Drug withdrawn,Ongoing,

--

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: CSV import for files with ' apostrophe in the text
553
August 11, 2020 04:02AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.