Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to import text, Excel and Access data into SQL Server2000. It is very detailed and has a certain reference value. Interested friends must finish reading it!

The code is as follows:

"AWU", "102300", "ZX240-3", "2609997000", "2609997000", "3016924000", "", "3091775000", "QCR0000285", "" AYE "," 001800 "," ZX60 "," 2609997000 "," 2609997000 "," 3016924000 ",", "

Although the project has developed an application to parse similar text files and store records in a database, it is obviously not as efficient as bulk import. To save time, starting with 9:00PM, decide to try to import the text file into Excel first, and then import Excel into SQL Server2000. (because some of the previous backup work is the reverse of the operation). Open Excel2007, and then select to open the text file of the data source, and the system automatically enters the text Import Wizard.

The first step is to select the field segmentation method. In this example, the "split symbol" is used, and the text file encoding method (UTF-8, ANSI, etc.) is appropriately adjusted to ensure that the imported data is not garbled.

The second step is to select split symbols and text recognition symbols (great! I was still thinking about how to remove the double quotation marks.

The third step is to set each column data type (general, text, date). Click finish! Here are two details:

1. For example, if the second field "001800" in the second row of the sample data is imported according to the "regular" type in the third step of import, the front end "00" will be removed, similar to the numerical treatment method. If you want to retain it at this time, please select the "text" type.

two。 Sometimes there will be format problems in the source data, especially when importing strongly constrained database forms. At this point, you have to go back and check the data in the Excel based on the error message. During the test, a comma appeared inside the "," double quotation marks, causing the data not to be imported, and so on. Multiple double quotation marks phenomenon: "" and so on.

3. After the data is imported into Excel, it is best to insert a row before the first row and enter the field name of the form corresponding to the database. Regardless of whether this line can be implemented, please prove in practice:) in view of the second problem mentioned above, it is recommended to import the text file into Access and SQL Server through Access. The advantage of this is that the data type of Access is more similar to that of SQL Server, so that when a text file is imported into Access, the validity of the data will be strictly checked and the subsequent troubles will be avoided.

The import method is similar. Here's a brief introduction:

Open Access2007 and select the external data-text file. After selecting the Chinese text file, the Import text Wizard also appears.

The first step is to select a fixed width or separator.

The second step is to select the separator type and text identifier, and optionally "the first line contains the field name".

The third step is to set the field name and field type (the most valuable one).

The fourth step is whether to establish the primary key.

Step five, finish it! A little bit of detail: when some text files are imported into the preview by Access, there will be a garbled code in front of the first line. It doesn't matter, open the source text file, enter at the beginning of the first line, and create a blank line. After importing the data, delete the first row record again in the Access table. Finally, through the SQL Server Enterprise Manager, select the form to be re-imported, and right-click to import data. Go on to the next step:) when SQL Server imports data, it will also strictly match whether the data types of records match. If not, the data import of this batch will fail. OK, share these first.

The above is all the contents of the article "how to import text, Excel, Access data into SQL Server2000". Thank you for reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.