# Fastest Way to Populate a Database?



## lightnb

I have a lot of infromation that needs to go into a database. It's at least 300 rows, each with 30-40 fields.

I have an online form setup, that I can fill out for one item, (one row), but It could take forever to do it this way.


Are there any faster methods? Maybey somthing that looks like a spreadsheet? Or can I upload from an Excel File?

Thanks,

Nick


----------



## brendandonhu

What format is the file in now, and what kind of database are you uploading to?


----------



## Rockn

The data you would be importing also needs to be consistent with the data or fields that already exist or script a make table procedure to import the data.


----------



## lightnb

The database is SQL, and the file format is book. As in- I'm holding a paperback book with all the information in it, and will have to hand key it all into the computer. 

I think it would be more tolerable to type it into one spreadsheet, than it would be to fill out and submit a PHP/HTML form for every single row.


----------



## thecoalman

Slightly off-topic but have you considered using OCR software for converting the text in the book to editable text? It would be a lot easier than manually typing it all in.


----------



## brendandonhu

If you're talking about MySQL, you can use Excel to save it as a CSV file then import that into the database with phpMyAdmin.


----------



## lightnb

thecoalman said:


> Slightly off-topic but have you considered using OCR software for converting the text in the book to editable text? It would be a lot easier than manually typing it all in.


Would you have to scan each page in first? That in itself could be very time consuming as well.



brendandonhu said:


> If you're talking about MySQL, you can use Excel to save it as a CSV file then import that into the database with phpMyAdmin.


Yes, it's MySQL. Is SQL somthing else? So if I use the excel method, I just have to make sure my fields have the exact same titles as in the database? Do the fields have to be in the same order from left to right, or does it figure out where they go if the names are the same?

Thanks,

Nick


----------



## Rockn

Ehhh, I wouldn't even venture down the OCR road if you care about the data integrity.

As long as the headings for the data fields in Excel are the same as the ones in your table the import should work just fine. They do not have to be in the same order as the ones in MySQL just the same.


----------



## lightnb

I created a test file with all the fields, but only the first 6 rows to test the import.

I saved it as CSV, and tried to upload it via phpMyAdmin, but got an error:



Code:


SQL query:

KEY , TYPE , lens, lens_unit, make, model_no, spot_beam_spread_beam, spot_beam_spread_field, flood_beam_spread_beam, flood_beam_spread_field, lamp_base_type, standard_lamp, weight, length, width, height, spot_focus_mf, spot_focus_pc, flood_focus_mf, flood_focus_pc, lamp_mount, pattern slot, accessory_slot, color_frame_size, shutters, ers_mf, ers_pc, ers_beam_angle, ers_field_angle, accessories1, Fresnel, 3, inches, Altman Stage Lighting, 100, 10, 23.5, 34, 55, D.C.Bayonet Candelabra, ESP -150W . , NOT Specified, "4.25""", "5.25""", "7.5""", 0.4, "6,125", 1.04, "1,950" , , no, no, "3.875""", no , , , , , "2-way Barn Door, Top-Hat, Motorized Color Wheel"2, Fresnel, 3, inches, Arriflex Corperation, 531300, 16 , , 60 , , 2 - Pin Prefocus, FKW - 300W . , 6.5 , , , , 0.28, "15,000", 1.15, "4,250" , , no, no, "5""", no , , , , , "4-Way Barn Door, Top Hat"3, Fresnel, 3, inches, Berkey Colortran, 214 -002, 10, 20, 36, 48, D.C.Bauonet Candelabra, 176 -188 -150W . , 2.3, "5""", "5.5""", "7.25""", 0.35, "4,800", 0.9, "1,296" , , no, no , , no , , , , , 4 - Way Barn Door4, Fresnel, 3, inches, Berkey Colortran, 214 -005, 10, 20, 36, 48, D.C.Bauonet Candelabra, 176 -188 -150W . , 2.3, "5""", "5.5""", "7.25""", 0.35, "4,800", 0.9, "1,296" , , no, no , , no , , , , , 4 - Way Barn Door5, Fresnel, 3, inches, Berkey Colortran, 214 -006, 10, 20, 36, 48, D.C.Bauonet Candelabra, 176 -188 -150W . , 2.3, "5""", "5.5""", "7.25""", 0.35, "4,800", 0.9, "1,296" , , no, no , , no , , , , , 4 - Way Barn Door6, Fresnel, 3, inches, Berkey Colortran, 214 -007, 10, 20, 36, 48, D.C.Bauonet Candelabra, 176 -188 -150W . , 2.3, "5""", "5.5""", "7.25""", 0.35, "4,800", 0.9, "1,296" , , no, no , , no , , , , , 4 - Way Barn Door

MySQL said: Documentation
#1064 - You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'key,type,lens,lens_unit,make,model_no,spot_beam_spread_beam,spo

I have attached the Excel file - The site wouldn't let me upload the CSV.


----------



## Rockn

You can probably nix the "key" field as that is probably a primary key field that will automatically be generated when the other fields are imported.


----------



## lightnb

I droped the key field, and am now getting:



HTML:


Invalid field count in CSV input on line 1.


----------



## Rockn

Is there an actual "key" field in the table? Just leave the key field in and take all of the data out of it. If it is an number field that auto increments it should be fine.


----------



## lightnb

what should my key field be setup as? ie. type, length, values, etc?


----------



## Rockn

I don't think it matters in the spreadsheet as long as the field is set up in the table with the same name, the DB will take care of the sizing and type.


----------



## lightnb

the names are the same, but im still getting "Invalid field count in CSV input on line 1."


----------



## thecoalman

lightnb said:


> Would you have to scan each page in first? That in itself could be very time consuming as well.


Yes, but even if your a very fast typist it would still be quicker. My typing skills
are terrible so I would have to use OCR for a project like that. As far as the data
integrity Rockn mentioned that's true to some extent but unless it's imperative that
you have an exact word for word duplication I wouldn't be too concerned about
it. Spell checker will pick up nearly every mistake, even ones in the original
document if present...  The only ones you're going to miss are ones it read incorrectly
but are words. You could of course proof it afterwards.


----------



## Rockn

If you are getting an invalid field count you either have too many fields or not enough in the CSV file. It has to match the table exactly.


----------



## lightnb

I have exactly five fields in the table and exactly five colums in my spread sheet. They all have the same names. But it's still giving that error message.

If i use "CSV using LOAD DATA" as my 'format of imported file'. IT appears to load correcly, and creates the correct number of rows, but theres no data in any of the rows except for the key field.

The plain 'CSV' import option has choices such as 'replace table data with file', 'ignore duplicate rows', fields terminated by ;', 'fields enclosed by " ', 'fields escaped by \', 'lines terminated by auto', and 'colum names'. Do these need to be changed?


----------



## lightnb

Here is the contents of the CSV file:



Code:


key,color_number,color_name,transmission_value_percent,correction
1,00,Clear,100,
2,01,Light ******* Amber,56,
3,02,******* Amber,78,
4,03,Dark ******* Amber,62,
5,04,Medium ******* Amber,66,
6,304,Pale Apricot,79,
7,05,Rose Tint,80,
8,305,Rose Gold,75,
9,3410,Roscosun 1/8 CTO,,5500K to 4900K
10,3409,Roscosun 1/4 CTO,,5500K to 4500K
11,3408,Roscosun 1/2 CTO,,5500K to 3800K
12,3411,Roscosun 3/4 CTO,58,
13,3407,Roscosun CTO,,5500K to 2900K
14,06,No Color Straw,92,
15,07,Pale Yellow,96,
16,08,Pale Gold,86
17,09,Pale Amber Gold,74
18,4515,CalColor 15 Yellow,80
19,4530,CalColor 30 Yellow,75
20,4560,CalColor 60 Yellow,70
21,4590,CalColor 90 Yellow,66
22,10,Medium Yellow,92
23,310,Daffodil,82
24,11,Light Straw,82
25,12,Straw,88
26,312,Canary,85
27,2003,Storaro Yellow,64
28,13,Straw Tint,78
29,14,Medium Straw,68
30,15,Deep Straw,65
31,16,Light Amber,68
32,316,Gallo Gold,58
33,17,Light Flame,56
34,317,Apricot,51
35,18,Flame,56
36,318,Mayan Sun,52
37,19,Fire,20
38,20,Medium Amber,54
39,21,Golden Amber,43
40,321,Soft Golden Amber,39
41,2002,Storaro Orange,23
42,22,Deep Amber,26
43,23,Orange,32
44,24,Scarlet,22
45,25,Orange Red,14
46,4615,CalColor 15 Red,67
47,4630,CalColor 30 Red,55
48,4660,CalColor 60 Red,39,
49,4690,CalColor 90 Red,30,
50,26,Light Red,12,
51,27,Medium Red,4,
52,30,Light Salmon Pink,44,
53,31,Salmon Pink,46,
54,32,Medium Salmon Pink,28,
55,332,Cherry Rose,38,
56,33,No Color Pink,65,
57,333,Blush Pink,71,
58,3318,Tough 1/8 Minus Green,,Partial Green Absorbing Filter
59,3314,Tough 1/4 Minus Green,,Partial Green Absorbing Filter
60,3313,Tough 1/2 Minus Green,,Partial Green Absorbing Filter
61,3308,Tough Minus Green,,Converts Fluorescents to Daylight
62,34,Flesh Pink,45,
63,35,Light Pink,66,
64,4815,CalColor 15 Pink,71
65,4830,CalColor 30 Pink,61
66,4860,CalColor 60 Pink,46
67,4890,CalColor 90 Pink,38
68,36,Medium Pink,46
69,336,Billington Pink,48
70,37,Pale Rose Pink,56
71,337,True Pink,55
72,4715,CalColor 15 Magenta,65
73,4730,CalColor 30 Magenta,52
74,4760,CalColor 60 Magenta,37
75,4790,CalColor 90 Magenta,28
76,38,Light Rose,49
77,39,Skelton Exotic Sangria,10
78,339,Broadway Pink,15
79,40,Light Salmon,34
80,41,Salmon,24
81,42,Deep Salmon,8
82,342,Rose Pink,16
83,43,Deep Pink,28
84,343,Neon Pink,33
85,44,Middle Rose,26
86,344,Follies Pink,21
87,45,Rose,8
88,46,Magenta,6
89,346,Tropical Magenta,22
90,47,Light Rose Purple,16
91,48,Rose Purple,16
92,49,Medium Purple,4
93,349,Fisher Fuchsia,11
94,50,Mauve,14
95,51,Surprise Pink,54
96,351,Lavender Mist,61
97,52,Light Lavender,26
98,53,Pale Lavender,64
99,54,Special Lavender,50
100,4915,CalColor 15 Lavender,61
101,4930,CalColor 30 Lavender,47
102,4960,CalColor 60 Lavender,29
103,4990,CalColor 90 Lavender,18
104,55,Lilac,37
105,355,Pale Violet,20
106,56,Gypsy Lavender,4
107,356,Middle Lavender,27
108,57,Lavender,24
109,357,Royal Lavender,5
110,58,Deep Lavender,10
111,358,Rose Indigo,5
112,2009,Stararo Violet,3,
113,2008,Stararo Indigo,4,
114,59,Indigo,2,
115,359,Medium Violet,Not Specified,
116,3216,Eighth Blue,,boosts 3200K to 3300 K
117,3208,Quarter Blue,,boosts 3200K to 3500 K
118,3206,Third Blue,,boosts 3200K to 3800 K
119,3204,Half Blue,,boosts 3200K to 4100 K
120,3203,Three Quarter Blue,41,
121,3202,Full Blue,,boosts 3200K to 5500 K
122,3220,Double Blue,10,
123,4215,CalColor 15 Blue,57,
124,4230,CalColor 30 Blue,42,
125,4260,CalColor 60 Blue,24,
126,4290,CalColor 90 Blue,15,
127,60,No Color Blue,62,
128,360,Clearwater,52
129,61,Mist Blue,66
130,62,Booster Blue,54
131,362,Tipton Blue,32
132,63,Pale Blue,56
133,363,Aquamarine,52
134,64,Light Steel Blue,26
135,364,Blue Bell,32
136,65,Daylight Blue,35
137,365,Tharon Delft Blue,36
138,66,Cool Blue,67
139,67,Light Sky Blue,26
140,367,Slate Blue,20
141,68,Sky Blue,14
142,69,Brilliant Blue,18
143,70,Nile Blue,45
144,370,Italian Blue,31
145,71,Sea Blue,30
146,72,Azure Blue,44
147,4315,CalColor 15 Cyan,72
148,4330,CalColor 30 Cyan,63
149,4360,CalColor 60 Cyan,50
150,4390,CalColor 90 Cyan,39
151,73,Peacock Blue,28
152,74,Night Blue,4
153,76,Light Green Blue,9
154,376,Bermuda Blue,6
155,77,Green Blue,9
156,78,Trudy Blue,19
157,378,Alice Blue,15
158,2007,VS Blue,10
159,79,Bright Blue,8
160,80,Primary Blue,9
161,81,Urban Blue,10
162,82,Surprise Blue,6
163,382,Congo Blue,0.56
164,83,Medium Blue,4
165,383,Sapphire Blue,4
166,84,Zephyr Blue,14
167,85,Deep Blue,3
168,385,Royal Blue,4
169,86,Pea Green,56
170,386,Leaf Green,32
171,87,Pale Yellow Green,85
172,4415,CalColor 15 Green,67
173,4430,CalColor 30 Green,52
174,4460,CalColor 60 Green,32
175,4490,CalColor 90 Green,18
176,3317,Tough 1/8 Plus Green,,Adds green to balance with Fluorescents
177,3316,Tough 1/4 Plus Green,,Adds green to balance with Fluorescents
178,3315,Tough 1/2 Plus Green,,Adds green to balance with Fluorescents
179,3304,Tough Plus Green,,To Balance Daylight with Fluorescents
180,88,Light Green,82,
181,388,Gaslight Green,76,
182,89,Moss Green,45,
183,389,Chroma Green,40,
184,2004,Stararo Green,15,
185,90,Dark Yellow Green,13,
186,91,Primary Green,7,
187,92,Turquoise,59,
188,93,Blue Green,35,
189,94,Kelly Green,25,
190,95,Medium Blue Green,15,
191,395,Teal Green,13,
192,96,Lime,98
193,97,Light Grey,50
194,397,Pale Grey,70
195,98,Medium Grey,25
196,99,Chocolate,35
197,100,Frost,
198,101,Light Frost,
199,102,Light Tough Frost,
200,103,Tough Frost,
201,104,Tough Silk,
202,105,Tough Spun,
203,106,Light Tough Spun,
204,111,Tough Rolux,
205,112,Opal Tough Frost,
206,113,Matte Silk,
207,114,Hamburg Frost,
208,115,Light Tough Rolux
209,116,Tough White Diffusion
210,117,Tough 1/2 White Diffusion
211,118,Tough 1/4 White Diffusion
212,119,Light Hamburg Frost
213,120,Red Diffusion
214,121,Blue Diffusion
215,122,Green Diffusion
216,124,Red Cyc Silk
217,125,Blue Cyc Silk
218,126,Green Cyc Silk
219,127,Amber Cyc Silk
220,132,Quarter Hamburg Frost
221,160,Light Tough Silk
222,162,Light Opal
223,163,Powder Frost


----------



## lightnb

I changed the 'fields terminated by' option from ' ; ' to ' , '

and now it imports the first 16 rows correctly, and then gives the error:



HTML:


Invalid field count in CSV input on line 17.


----------



## lightnb

I opened the CSV file in a text editor and added commas to the end of every single line that didn't have one, and this seemed to fix the problem- I now get the message



HTML:


Import has been successfully finished, 224 queries executed.

But I'm still not convinced... If I click browse in phpMyAdmin, I can only see the first 15 rows.

All the other rows are there, but they have no data except for the key. How do I know if it all imported correctly?


----------



## Rockn

THe consistency of the data you are importing seems a little off as I can get import errors trying to bring it into Access. There is the text "not specified" in a numeric field as well as a decimal value 0.56 both in the transmission_value_percent field. Importing numbers in the primary key field will also not work if there is already an existing value that is the same.


----------



## lightnb

I figured out what was going on - It had already assigned a bunch of blank keys from a previous failed attempt at the upload, and it was importing all the new data starting at row two-hundred and somthing- Which is why all the blank keys were showing up.

I had to empty the entire table, and run the import again.

The key to getting the import to work was to [1] have a 'key' field in the CSV file that contained no data, and also to add a comma to the end of every line that didn't have a value for the correction field.

It now seems to have uploaded corectly, and I can query it and have it spit out all the contents as expected.


----------



## Rockn

I was kind of wondering if it would import the empty fields correctly since there was no comma seperating an empty field and having vaues entered in the key filed would create duplicates. Glad it's working now.


----------

