将数据从excel文件导入sql server数据库

d7v8vwbk  于 2021-10-10  发布在  Java
关注(0)|答案(1)|浏览(338)

我想使用laravel将数据从excel文件导入sql server数据库,但出现此错误
{
sqlstate[22007]:[microsoft][odbc driver 17 for sql server][sql server]la conversion d'un type de données nvarchar en type de données datetime a créune valeur hors limites(sql:插入到[用户]([姓名],[电子邮件],[密码],[更新地址],[创建地址])
}
注意:当我将同一个文件导入到一个具有相同属性的mysql表时,这是正常的。但当我想在SQLServer数据库中执行相同的操作时,会收到错误消息

  1. (userimport)
  2. <?php
  3. namespace App\Imports;
  4. use App\Models\User;
  5. use Maatwebsite\Excel\Concerns\ToModel;
  6. use Maatwebsite\Excel\Concerns\WithHeadingRow;
  7. use Hash;
  8. class UserImport implements ToModel,WithHeadingRow
  9. {
  10. /**
  11. * @param array $row
  12. *
  13. * @return \Illuminate\Database\Eloquent\Model|null
  14. */
  15. public function model(array $row)
  16. {
  17. return new User([
  18. 'name' => $row['name'],
  19. 'email' => $row['email'],
  20. 'password' => Hash::make('password')
  21. ]);
  22. }
  23. }
  24. ----------
  25. (Controller)
  26. public function import(Request $request)
  27. {
  28. //$file = $request->file('file');
  29. Excel::import(new UserImport,$request->file);
  30. //Excel::import(new UserImport,request()->file('file'));
  31. }
  32. ----------
  33. (model)
  34. protected $fillable = [
  35. 'name',
  36. 'email',
  37. 'password',
  38. ];
  39. that's my code source from 3 classes(model,controller,Userimport)
hmtdttj4

hmtdttj41#

我不知道什么是laravel,但如果您使用的是excel和sql server,您可以利用一些vba代码来完成提升。

  1. Sub InsertInto()
  2. 'Declare some variables
  3. Dim cnn As adodb.Connection
  4. Dim cmd As adodb.Command
  5. Dim strSQL As String
  6. 'Create a new Connection object
  7. Set cnn = New adodb.Connection
  8. 'Set the connection string
  9. cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=your_database_name;Data Source=your_server_name"
  10. 'Create a new Command object
  11. Set cmd = New adodb.Command
  12. 'Open the Connection to the database
  13. cnn.Open
  14. 'Associate the command with the connection
  15. cmd.ActiveConnection = cnn
  16. 'Tell the Command we are giving it a bit of SQL to run, not a stored procedure
  17. cmd.CommandType = adCmdText
  18. 'Create the SQL
  19. strSQL = "UPDATE TBL SET JOIN_DT = '2013-01-22' WHERE EMPID = 2"
  20. 'Pass the SQL to the Command object
  21. cmd.CommandText = strSQL
  22. 'Execute the bit of SQL to update the database
  23. cmd.Execute
  24. 'Close the connection again
  25. cnn.Close
  26. 'Remove the objects
  27. Set cmd = Nothing
  28. Set cnn = Nothing
  29. End Sub

最后,设置适当的引用。

展开查看全部

相关问题