As shown below:
Copy Code code as follows:
"AWU", "102300", "zx240-3", "2609997000", "2609997000", "3016924000", "" "," 3091775000 "," "," "," QCR0000285 "," ""
"AYE", "001800", "ZX60", "2609997000", "2609997000", "3016924000", "", "", "", "", "" "," ""
Although projects develop applications, parse similar text files, and store records in a database, they are obviously less efficient than bulk imports. To save time, starting with 9:00pm, you decide to try importing text files into Excel before you import Excel into SQL Server2000. (since part of the previous backup work is the reverse way of the operation).
Open Excel2007, then select the text file to open the data source, and the system automatically enters the Text Import Wizard.
The first step, select the field segmentation method, this example uses "split symbol", the appropriate adjustment text file encoding method (UTF-8, ANSI, etc.), has guaranteed that the import data is not garbled.
The second step is to select the split symbol, the text recognition symbol (great!). I was thinking about how to remove the double quotes.
The third step is to set each column data type (general, text, date). Click Finish!
A little hint of two detail questions:
1. If the second row of the example data in the second field "001800", if the third step in the import by the "General" type, then the front end "00" will be removed, similar to the number of processing, if you want to keep, select the text type.
2. Sometimes there are formatting problems in the source data, especially when importing strongly constrained database forms. Then you have to look back at the data in Excel based on the error message. The test encountered a comma appearing in the "," double quotes inside, causing the data to not import, and so on. Multiple double quote phenomena: "" and so on.
3. After data is imported into Excel, it is a good idea to insert a row before the first row and enter the field name of the corresponding form in the database. Do not set up this line can be implemented, but also ask you to prove in practice:)
In the second question above, it is recommended that you import the text file into Access and import SQL Server through access. The advantage of this is that access has a much more similar data type than SQL Server, so that when a text file is imported into Access, the legality of the data is rigorously checked to avoid any subsequent hassles.
The import method is also similar, simple introduction: Open Access2007, select External Data-text file, select text file, also appear the Import Text Wizard.
The first step is to select a fixed width or separator character.
In the second step, select the separator type and text identifier, and the option "first row contains field names."
The third step is to set the field name and field type (one of the most valuable).
Fourth step, whether to establish a primary key.
Fifth step, Finish!
A little hint of detail:
Some text files will have a garbled first line at the front end when access imports a preview. It doesn't matter, open the source text file, enter at the beginning of the first line, and create a blank line. Once you have imported the data, delete the first row records again in the Access table.
Finally, through SQL Server Enterprise Manager, select the form that you want to re-import, and right-click to select the import data. Proceed to the next step: SQL Server will also strictly match the data type of the record when importing data, and if it does not match, the data import for this batch fails.
OK, let's share this first. More tips, welcome to join us to enrich. The flexibility of the Office family and the skillful use of MS Software are really powerful and need to be dug up slowly: SQL Server to import data more skills, I also need to learn more!
