Use php to import text data to SQL SERVER

Source: Internet
Author: User

Use php to import text data to SQL SERVER

We often encounter using php to import text files to mysql, But how should we import them to sqlserver? I will share with you the operation method below, and I feel that the efficiency is not bad, we recommend it to you here.

CSV, SQL, and TXT supported in my tests

In fact, other formats are acceptable, but I cannot use them.

The speed is almost 10 thousand per second.

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

<? Php

Set_time_limit (0 );

Ignore_user_abort (true );

Include ('sgk. php ');

Echo "<br> ";

?>

<? Php

Function array_iconv ($ data, $ output = 'gbk '){

$ Encode_arr = array ('utf-8', 'ascii ', 'gbk', 'gb2312', 'big5', 'jis', 'eucjp-win ', 'sjis-win', 'euc-jpc ');

$ Encoded = mb_detect_encoding ($ data, $ encode_arr); // automatically determines the encoding.

If (! Is_array ($ data )){

Return mb_convert_encoding ($ data, $ output, $ encoded );

}

Else {

Foreach ($ data as $ key => $ val ){

If (is_array ($ val )){

$ Data [$ key] = array_iconv ($ val, $ input, $ output );

} Else {

$ Data [$ key] = mb_convert_encoding ($ data, $ output, $ encoded );

}

}

Return $ data;

}

}

Function microtime_float (){

Return microtime (true );

}

If ($ _ SESSION ['id'] <> ''){

If (@ $ _ POST ["submit"] <> ""){

If ($ _ FILES ["file"] ["type"] = "text/plain ")

| ($ _ FILES ["file"] ["type"] = "application/vnd. ms-excel ") | ($ _ FILES [" file "] [" type "] =" application/octet-stream ")

| ($ _ FILES ["file"] ["type"] = "application/vnd. openxmlformats-officedocument.spreadsheetml.sheet "))

& ($ _ FILES ["file"] ["size"] <198102805 ))

{

If ($ _ FILES ["file"] ["error"]> 0)

{

Echo "Return Code:". $ _ FILES ["file"] ["error"]. "<br/> ";

}

Else

{

$ Name = $ _ FILES ["file"] ["name"];

$ Name = iconv ('utf-8', 'gb2312 // IGNORE ', $ _ FILES ['file'] ['name']);

Echo "Upload:". $ _ FILES ["file"] ["name"]. "<br/> ";

Echo "Size:". ($ _ FILES ["file"] ["size"]/1048576). "Mb <br/> ";

$ Exit = pathinfo ($ _ FILES ["file"] ["name"], PATHINFO_EXTENSION );

$ Exit = '.'. $ exit;

 

If ($ exit = ". asp" or $ exit = ". php" or $ exit = ". jsp" OR $ exit = ". aspx "){

Echo "I wipe, what do you want to do? ";

Exit;

}

$ Size = filesize ($ _ FILES ["file"] ["tmp_name"]);

$ _ FILES ["file"] ["name"] = md5 ($ _ FILES ["file"] ["name"]. $ size). $ exit;

If (file_exists ("uploadhehe/". $ _ FILES ["file"] ["name"])

{

Echo $ _ FILES ["file"] ["name"]. "already exists ";

}

Else

{

Move_uploaded_file ($ _ FILES ["file"] ["tmp_name"],

"Uploadhehe/". $ _ FILES ["file"] ["name"]);

Echo "<br> Start Automatic File Processing <br> ";

$ Begin = microtime_float ();

$ File = "uploadhehe/". $ _ FILES ["file"] ["name"];

$ Handle = @ fopen ($ file, "r ");

// $ Handle = array_iconv ('utf-8', 'gbk', $ handle );

$ Total = 0;

$ Over = 0;

If ($ handle ){

$ SQL = "INSERT INTO [dbo]. [own_user_data1] ([Name], [Context], [md5], [data]) VALUES ";

$ Num = 0;

$ _ FILES ['file'] ['name'] = iconv ('utf-8', 'gb2312 // IGNORE ', $ _ FILES ['file'] ['name']);

While (! Feof ($ handle )){

$ Buffer = fgets ($ handle, 2000 );

$ Buffer = str_replace ("\ n", "", $ buffer );

$ Line_array = explode ("\ r \ n", $ buffer );

$ Context = htmlspecialchars (addslashes ($ line_array [0]);

$ Context = str_replace ("'",', ', $ Context );

$ Context = str_replace ('"', $ Context );

$ Context = array_iconv ($ Context );

$ Date = date ('Y-m-d h: I: s', time ());

If ($ num <999 ){

$ SQL. = "('{$ name}', '{$ Context}', '{$ _ FILES ['file'] ['name']}', '{$ date }'),";

} Else {

$ SQL. = "('{$ name}', '{$ Context}', '{$ _ FILES ['file'] ['name']}', '{$ date }')";

$ A = sqlsrv_query ($ conn, $ SQL );

// If ($ a === false ){

// Die (print_r (sqlsrv_errors (), true ));

//}

$ Num = 0;

$ SQL = "INSERT INTO [dbo]. [own_user_data1] ([Name], [Context], [md5], [data]) VALUES ";

 

}

$ Num ++;

$ Total ++;

 

}

}

Fclose ($ handle );

$ Sql1 = "insert into [dbo]. [own_user_info] ([uid], [md5], [date], [name], [size], [gold]) VALUES ('{$ _ SESSION ["id"]}', '{$ _ FILES ["file"] ["name"]}', '{$ date }', '{$ name}', '{$ total}', '1 ')";

Sqlsrv_query ($ conn, $ sql1 );

If ($ a = false ){

Die (print_r (sqlsrv_errors (), true ));

}

// Process records not inserted into the database

If (substr ($ SQL, strlen ($ SQL)-1,1) = ","){

$ SQL = substr ($ SQL, 0, strlen ($ SQL)-1 );

Sqlsrv_query ($ conn, $ SQL );

}

$ End = microtime_float ();

$ Time = $ end-$ begin;

Echo "A total of {$ total} records were imported, which took {$ time} seconds ";

 

}

}

}

Else

{

Echo "Upload Failed! ";

 

}

}

} Else {

Header ("Location: login. php ");

}

?>

The above is all the content of this article. I hope you will like it.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.