I want to upload some data files from the Ministry of Education (Microdata of the Census of Higher Education 2012) in my SQL Server 2012 Express.
The data is in fixed column files generated by some program written in Fortran.
I created a table following the data dictionary and I used bcp to generate a file format:
bcp test.dbo.IES format nul -S SERVIDOR -c -f C:\Users\livisghton\Desktop\IES.fmt -T
When I use bcp to load the data, the following happens:
C: \ Users \ lksa> bcp MCES_2012.dbo.IES IN C: \ Users \ lksa \ Desktop \ i_sample_txt -f ies.fmt -T -c Warning: -c replaces -f.
Starting copy ... SQLState = S1000, NativeError = 0 Error = [Microsoft] [SQL Server Native Client 11.0] Unexpected EOF Found in BCP Data File
0 lines copied. Network packet size (bytes): 4096 Total clock time (ms): 1
Any idea what's going wrong?
PS: I am logged on via Windows authentication, the file is on the desktop, the SQL Server account has access to my desktop and I am in an administrative account. The complete file is in ChopApp .
As far as I know, the data is in Unicode over UTF-8.
The first three lines of the file are:
1310FACULDADE DE AMERICANA 872 5Privada sem fins lucrativos 3Faculdade 3501608AMERICANA 35SPSudeste 0 127 6 9 7 9 17 42 13 19 0 4 0 1 0 0 1 1 2 23316250.20 0.00 425201.80 3691699.00 3851526.20 1418442.40 12746316.20 255342.00 0.00 3235786.40
4358INSTITUTO FEDERAL DE EDUCAÇÃO, CIÊNCIA E TECNOLOGIA DO SUL DE MINAS GERAIS 8622 1Pública Federal 4Instituto Federal de Educação, Ciência e Tecnologia 3152501POUSO ALEGRE 31MGSudeste 0 394 17 2 14 1 57 12 2 2 50 28 90 97 13 9 1 1 1 2675232.00 141722770.40 23902.80 33779272.80 21537198.60 6213781.40 36152500.80 44326708.00 1112226.80 0.00
790FACULDADE DE ENFERMAGEM DO HOSPITAL ISRAELITA ALBERT EINSTEIN 542 5Privada sem fins lucrativos 3Faculdade 3550308SAO PAULO 35SPSudeste 1 28 0 0 0 0 4 10 4 6 2 2 0 0 0 0 1 1 1 2665000.00 0.00 0.00 2014000.00 160000.00 402750.00 708000.00 2750000.00 0.00 0.00
The format file generated by bcp is:
11.0
41
1 SQLCHAR 0 8 "\t" 1 CO_IES ""
2 SQLCHAR 0 200 "\t" 2 NO_IES Latin1_General_CI_AS
3 SQLCHAR 0 8 "\t" 3 CO_MANTENEDORA ""
4 SQLCHAR 0 8 "\t" 4 CO_CATEGORIA_ADMINISTRATIVA ""
5 SQLCHAR 0 100 "\t" 5 DS_CATEGORIA_ADMINISTRATIVA Latin1_General_CI_AS
6 SQLCHAR 0 8 "\t" 6 CO_ORGANIZACAO_ACADEMICA ""
7 SQLCHAR 0 100 "\t" 7 DS_ORGANIZACAO_ACADEMICA Latin1_General_CI_AS
8 SQLCHAR 0 8 "\t" 8 CO_MUNICIPIO_IES ""
9 SQLCHAR 0 150 "\t" 9 NO_MUNICIPIO_IES Latin1_General_CI_AS
10 SQLCHAR 0 8 "\t" 10 CO_UF_IES ""
11 SQLCHAR 0 2 "\t" 11 SGL_UF_IES Latin1_General_CI_AS
12 SQLCHAR 0 30 "\t" 12 NO_REGIAO_IES Latin1_General_CI_AS
13 SQLCHAR 0 8 "\t" 13 IN_CAPITAL_IES ""
14 SQLCHAR 0 8 "\t" 14 QT_TEC_TOTAL ""
15 SQLCHAR 0 8 "\t" 15 QT_TEC_FUND_INCOMP_MASC ""
16 SQLCHAR 0 8 "\t" 16 QT_TEC_FUND_INCOMP_FEM ""
17 SQLCHAR 0 8 "\t" 17 QT_TEC_FUND_COMP_MASC ""
18 SQLCHAR 0 8 "\t" 18 QT_TEC_FUND_COMP_FEM ""
19 SQLCHAR 0 8 "\t" 19 QT_TEC_MEDIO_MASC ""
20 SQLCHAR 0 8 "\t" 20 QT_TEC_MEDIO_FEM ""
21 SQLCHAR 0 8 "\t" 21 QT_TEC_SUPERIOR_MASC ""
22 SQLCHAR 0 8 "\t" 22 QT_TEC_SUPERIOR_FEM ""
23 SQLCHAR 0 8 "\t" 23 QT_TEC_ESPEALIZACAO_MASC ""
24 SQLCHAR 0 8 "\t" 24 QT_TEC_ESPEALIZACAO_FEM ""
25 SQLCHAR 0 8 "\t" 25 QT_TEC_MESTRADO_MASC ""
26 SQLCHAR 0 8 "\t" 26 QT_TEC_MESTRADO_FEM ""
27 SQLCHAR 0 8 "\t" 27 QT_TEC_DOUTORADO_MASC ""
28 SQLCHAR 0 8 "\t" 28 QT_TEC_DOUTORADO_FEM
29 SQLCHAR 0 8 "\t" 29 IN_ACESSO_PORTAL_CAPES ""
30 SQLCHAR 0 8 "\t" 30 IN_ACESSO_OUTRAS_BASES ""
31 SQLCHAR 0 8 "\t" 31 IN_REFERENTE ""
32 SQLCHAR 0 14 "\t" 32 VL_RECEITA_PROPRIA ""
33 SQLCHAR 0 14 "\t" 33 VL_TRANSFERENCIA ""
34 SQLCHAR 0 14 "\t" 34 VL_OUTRA_RECEITA ""
35 SQLCHAR 0 14 "\t" 35 VL_DES_PESSOAL_REM_DOCENTE ""
36 SQLCHAR 0 14 "\t" 36 VL_DES_PESSOAL_REM_TECNICO ""
37 SQLCHAR 0 14 "\t" 37 VL_DES_PESSOAL_REM_ENCARGO ""
38 SQLCHAR 0 14 "\t" 38 VL_DES_CUSTEIRO ""
39 SQLCHAR 0 14 "\t" 39 VL_DES_INSVESTIMENTO ""
40 SQLCHAR 0 14 "\t" 40 VL_DES_PESQUISA ""
41 SQLCHAR 0 14 "" 41 VL_DES_OUTRAS ""