PowerShell Problem Solver: Importing CSV Files into PowerShell

I see a lot of posts, questions and scripts that involve importing a CSV file into PowerShell. On one hand, working with a CSV file couldn’t be easier. On the other hand, PowerShell will happily turn any imported CSV file into a collection of objects, where working with those objects can be a little confusing if you don’t completely understand the process. I think part of the problem PowerShell beginners face is that they are still thinking about text, especially when importing a CSV file. Let me show you what I’m talking about.

Here’s a sample CSV file that includes test data I want to use in PowerShell.

I recommend you make sure the column entries in the header line do not have any spaces. It isn’t illegal, but it makes working with the resulting properties a bit more difficult as you have to quote the property name.

Next, let’s say I want to get results where the size is less than 0. Looking at the output, you might think this will work:

PowerShell

1

$data|where{$_.size-lt0}

Given the first screenshot you would expect to see at least Data_2 with a size value of -30. But this isn’t the case.

Grabbing the size of $data in PowerShell. (Image Credit: Jeff Hicks)

The only result is an object with nothing defined for the size property. When you face this situation, this is where Get-Member is useful. Exactly what type of objects are in $data?

Using Get-Member in Windows PowerShell. (Image Credit: Jeff Hicks)

Notice that all of the properties are treated as strings. This means that any operation that relies on something other than a string will fail to run properly.

The date sort has failed. (Image Credit: Jeff Hicks)

The date sort is wrong, and I shouldn’t be seeing anything with a test value of false. One way around this issue is to tell PowerShell to treat properties as a certain type. Let’s go back to my first filtering expression.

PowerShell

1

$data|where{$_.size-as[int]-lt0}|format-table

I’m using the –as operator and telling PowerShell to treat the size property as an integer. Now I get the result I expect.

You have to do this sort of type conversion whenever you are doing something with the property. However, using –as doesn’t always work. If you try this with my test data, then you’ll see that it won’t work.

PowerShell

1

$data|where{$_.Test-as[boolean]}|format-table

An alternative is to use one of the built-in methods to convert the value.

You’ll see that all of these methods require a parameter value, which helps PowerShell determine how to format the value. For our purpose, this System.IFormatProvider method comes down to two choices: a number or data.

PowerShell

1

2

[System.Globalization.NumberFormatInfo]::CurrentInfo

[System.Globalization.DateTimeFormatInfo]::CurrentInfo

The easiest way is to reference them directly with the .NET Framework. We already know that the size property will be treated as a string. But with a little help, we can tell PowerShell to treat it as an integer.

It is important for you to remember that you haven’t changed the underlying objects in $data, only how they are being processed in your PowerShell expression.

As you can see, I still have string properties.

String properties in Windows PowerShell. (Image Credit: Jeff Hicks)

One way to avoid all of this is to give your newly imported objects the proper type from the very beginning. Allow me to share an advanced function of mine called Import-TypedCSV.

Import-TypedCSV PowerShell function

PowerShell

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

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

#requires -version 3.0

FunctionImport-TypedCSV{

<#

.Synopsis

Import a CSV file with typed property values.

.Description

PowerShell will happily import any CSV file and turn it into an object. Unfortunately, PowerShell will treat everything as a string. This command will attempt to dynamically assign a type to each imported property.

Without going into extreme detail, I’ve tried to thoroughly document the function, where my command looks at the first line of data in the CSV file and attempts to determine the appropriate object type using regular expressions.

If any of the properties for the first object have no value, you will be prompted to specify the object type.

Prompting you to provide the object type in PowerShell. (Image Credit: Jeff Hicks)

But now that everything is properly typed, using the data is much easier.

I do have yet one more alternative you might want to consider, especially if you often import the same type of CSV file repeatedly. It is definitely an advanced technique, which I’ll guide you through in a future article.

MEMBER LOGIN:

BECOME A PETRI MEMBER:

About the Contributor

Jeffery Hicks is an IT veteran with over 25 years of experience, much of it spent as an IT infrastructure consultant specializing in Microsoft server technologies with an emphasis in automation and efficiency. He is a multi-year recipient of the Microsoft MVP Award in Windows PowerShell. He works today as an independent author, teacher and consultant. Jeff has written for numerous online sites and print publications and is a frequent speaker at technology conferences and user groups. His latest book is PowerShell Scripting and Toolmaking.